SQL Constraints - Everything You Should Know

SQL, or Structured Query Language, is a powerful tool used for managing and manipulating databases. One of the management tools used to play with and investigate databases is SQL. One feature that ensures accuracy or validity in a database is the implementation of constraints that ensure data is correct and unchanged while being inserted or updated. Rules are set against a column in tables that define types of data insertion or updation or deletion. This is a blog, which will post different types of SQL constraints using syntax and various examples.

Table of Content

• What are SQL Constraints?
• Creating SQL Constraints
• Types of SQL Constraints
• Conclusion

What Are SQL Constraints?

SQL constraints are the restrictions on the type of data entered into a database table. Constraints are helpful in enforcing accuracy and reliability in the data through certain conditions. Constraints can be applied both in column level that is it is applied on a single column and in table level, which affects the column lists.Some of the common constraints are:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

Creating SQL Constraints

Constraints can be defined during the creation of a new table or added to an existing table using the CREATE TABLE or ALTER TABLE commands. Here is how to define constraints during table creation:

SQL:

CREATE TABLE table_name (
    column_name1 data_type(size) constraint_name,
    column_name2 data_type(size) constraint_name,
    ...
);

In this syntax:

‘table_name’ is the name of the table you want to create.
‘column_name’ is the name of the column you want to create.
‘data_type’ is the data type of the column.
‘size’ specifies the maximum size or length of the column.
‘constraint_name’ is the name you want to give to the constraint.

Alternatively, if you want to create constraints using the ALTER TABLE command, use the ADD CONSTRAINT clause. 

Here’s the syntax:

SQL:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
column_name data_type(size);

In this syntax:

‘table_name’ is the name of the table to which you want to add the constraint.
‘constraint_name’ is the name you want to give to the constraint.
‘column_name’ is the column’s name to which the constraint applies.
‘data_type’ represents the data type of the column.‘size’ specifies the maximum size or length of the column.

Types of Constraints in SQL

There are different types of SQL constraints to aim for the accuracy and consistency of data in a table. Here are some common types of constraints, where we will clarify your understanding of SQL constraint syntax and examples:

1.NOT NULL Constraint

The NOT NULL constraint means a column cannot contain a NULL value. It is very important for fields that are required to hold data.

Example

CREATE TABLE Students_intellipaat (
    ID int NOT NULL,
    Name varchar(50) NOT NULL,
    Age int
);

In this example, both ID and Name cannot be NULL.

2. UNIQUE Constraint

UNIQUE constraint makes all values in a column different from one another to avoid duplication entries.

Example:

CREATE TABLE Students_intellipaat (
    ID int NOT NULL UNIQUE,
    Name varchar(50),
    Email varchar(100) UNIQUE
);

Here, both ID and Email must be unique across all records.

3. PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table and combines the characteristics of both NOT NULL and UNIQUE.

Example:

CREATE TABLE Students_intellipaat (
    ID int PRIMARY KEY,
    Name varchar(50),
    Age int
);

In this case, ID serves as the primary key, ensuring it is unique and not null.

4. FOREIGN KEY Constraint

The FOREIGN KEY constraint creates the relationship between tables, linking an indexed column to a primary or UNIQUE key on another table.

Example:

CREATE TABLE Courses_intellipaat (
    CourseID int PRIMARY KEY,
    CourseName varchar(50)
);
CREATE TABLE Enrollments_intellipaat (
    EnrollmentID int PRIMARY KEY,
    StudentID int,
    CourseID int,
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

This establishes a relationship between Enrollments and Courses.

5. CHECK Constraint

The CHECK constraint allows you to define a condition that needs to be met before data can be inserted into a table.

Example:

CREATE TABLE Students_intellipaat (
    ID int PRIMARY KEY,
    Name varchar(50),
    Age int CHECK (Age >= 18)
);

This ensures that only students aged 18 or older can be added to the table.

6. DEFAULT Constraint

The DEFAULT constraint assigns a default value to a column when no value is inserted.

Example:

CREATE TABLE Students_intellipaat (
    ID int PRIMARY KEY,
    Name varchar(50),
    Age int DEFAULT 18
);

If no age is provided during insertion, it will default to 18.

Conclusion

The most important tool used in maintaining data integrity and accuracy within Microsoft SQL Server consists of SQL constraints. Having known about different constraints and how they are properly implemented, you would be able to design proper databases with the correct data management. This is your ultimate guide for SQL constraints which provides all that you would require in order to have rules on your database.