Exploring Constraints in SQL Server

Introduction on SQL Server

SQL Server is an RDBMS developed and maintained by Microsoft to support a wide variety of transaction processing, data storage and retrieval, etc. Constraints are used in SQL Server to prevent the insertion of unwanted data in the tables. In this article, we will study the constraints and different types of constraints available in SQL Server with the help of examples.

What are Constraints?

Constraints in SQL Server are predefined rules that you can enforce on a column or more than one column or table so that the undesirable does not get inserted into the tables. Constraints help to ensure data integrity, data accuracy, and data reliability of the values stored in the tables.

If you perform an SQL operation in SQL Server that meets specified constraint rule criteria, then the required operation gets performed successfully. However, in case of constraint violation by data, the SQL operation will be aborted with an error message.

For example, if you want to create a student table where the age of students must be greater than 18, you can use the CHECK Constraint as shown in the below query:

CREATE TABLE STUDENT(std_id INT PRIMARY KEY, std_name varchar(50),age INT CHECK (Age>18));

Now, if you try to run the below query you will not be able to insert the data due to the CHECK constraint.

INSERT INTO STUDENT VALUES (1, 'Rahul',12)

It gives us the below output.

Now, we’ll study about the different types of constraints in SQL Server.

Types of Constraints

1. NOT NULL Constraint

By default, columns in tables are allowed to store NULL values in SQL Server. The NOT NULL constraint in a column ensures that NULL values are not accepted as an input for that column. It means that if NOT NULL constraint is specified on a column, then users cannot insert a new record or update the already existing record without adding a value to that column.

For example, if you want to create a student table where the data in any column must not be NULL, you can use the NOT NULL Constraint as shown in the below query:

CREATE TABLE STUDENT(std_id INT NOT NULL, std_name varchar(50) NOT NULL, age INT NOT NULL);

Now, if you try to run the below query you will not be able to insert the data due to NOT NULL constraint.

INSERT INTO STUDENT(std_id, age) VALUES (1,24)

It gives us the below output.

2. CHECK Constraint

The CHECK constraint specifies a valid range of input values that can be inserted into the specified table columns in SQL Server. When any SQL statement is provided to insert or update a value, the statement gets executed successfully only if the CHECK constraint is satisfied.

For example, if you want to create a student table where only the student data having valid zip codes must be stored in the table, you can use the CHECK Constraint as shown in the below query:

CREATE TABLE STUDENT(
std_id INT PRIMARY KEY, 
std_name varchar(50) NOT NULL, 
res_address varchar(50) NOT NULL,
zip_code INT CHECK (zip_code LIKE REPLICATE ('[0-9]', 5)));

Now, if you try to run the below query you will not be able to insert the data due to CHECK constraint.

INSERT INTO STUDENT VALUES (1, 'Rahul','49, JAY Nagar',900234);

It gives us the below output.

3. UNIQUE Constraint

The UNIQUE constraint ensures that you do not have duplicate values in the specified table columns in SQL Server. Whenever UNIQUE constraints are defined on the table, then the index is automatically created by SQL Server. Only one NULL value is acceptable in a column having a UNIQUE constraint.

For example, if you want to create a student table where only the student data having unique names must be stored in the table, you can use the UNIQUE Constraint as shown in the below query:

CREATE TABLE STUDENT(
std_id INT PRIMARY KEY, 
std_name varchar(50) UNIQUE, 
res_address varchar(50) NOT NULL,
);

Now, if you try to run the below query you will not be able to insert the data when std_id is 4, due to UNIQUE constraint.

INSERT INTO STUDENT VALUES (3, 'Raj','49, JAY Nagar');
INSERT INTO STUDENT VALUES (4, 'Raj','12, Prem Nagar');

It gives us the below output.

 

4. DEFAULT Constraint

The DEFAULT constraint is used to provide the default value for a column or more than one column in SQL tables. If we do not specify a value in the INSERT statement for the column with the DEFAULT constraint, SQL Server will insert its default assigned value in the table.

For example, if you want to create a student table where by default year_of_birth is taken as 1998 while inserting new student data, you can use the DEFAULT Constraint as shown in the below query:

CREATE TABLE STUDENT(
std_id INT PRIMARY KEY IDENTITY(1,1), 
std_name varchar(50), 
year_of_birth bigint DEFAULT 1998,
);

Now, if you try to run the below year_of_birth is automatically taken as 1998 due to the DEFAULT constraint.

INSERT INTO STUDENT(std_name) VALUES ('Ram');

It gives us the below output.

5. PRIMARY KEY Constraint

The PRIMARY KEY constraint is used to uniquely identify each row in a table in SQL Server. When we define a PRIMARY KEY constraint on a table, SQL Server automatically creates clustered index for that table. This constraint is used to ensure data integrity in a table.

For example, if you want to create a student table with std_id as PPRIMARY KEY then you can use the below query:

CREATE TABLE STUDENT(
std_id INT PRIMARY KEY IDENTITY(1,1), 
std_name varchar(50), 
year_of_birth bigint DEFAULT 1998,
);

 

6. FOREIGN KEY Constraint

To create relationship between two tables in SQL Server, FOREIGN KEY constraint is used. This constraint is used for enforcing referential integrity in SQL Server.

The column specified as foreign key in one table should have a corresponding entry in the referenced table primary key column. You cannot insert a value in a column with FOREIGN KEY constraint in the child table without inserting it in the parent table first.

For example, if you want to create a student table with std_id as PPRIMARY KEY and other table grades having std_id as FOREIGN KEY, then you can use the below queries:

CREATE TABLE STUDENT(
std_id INT PRIMARY KEY IDENTITY(1,1), 
std_name varchar(50), 
year_of_birth bigint DEFAULT 1998,
);
CREATE TABLE grades(
  std_id int not null FOREIGN KEY references STUDENT (std_id),
  grade varchar(3),
 primary key(std_id)
);

 

Conclusion

Constraints are predefined rules to be applied at column-level or table-level for restricting values that are allowed in the columns.

To summarize, the following were the major takeaways about the SQL Server Constraints:

1. We learned about how we can maintain data accuracy and data integrity using constraints.
2. We also got an understanding about what are the various types of constraints available in SQL Server.
3. We have seen how we can use CHECK constraint to specify a valid range of input value, to uniquely identify table rows using PRIMARY KEY constraint, etc.
4. Apart from this, we also learned about how to specify default value, not allow nulls in column values, etc.