Constraints in SQL Server

Back to: SQL Server Tutorial

Constraints in SQL Server with Examples:

In this article, I am going to discuss Constraints in SQL Server with examples. Please read our previous article where we discussed SQL Server Data Types in detail. As part of this article. we are going to discuss the following things in detail.

1. What is Data Integrity?
2. What are SQL Server constraints?
3. Why do we need SQL Server constraints?
4. Different types of constraints in SQL Server?
5. Understanding NULL in SQL Server
6. What is Default, Not Null, Unique and Check Constraint in SQL Server?
7. How to Impose Constraint on Table.
8. Imposing Constraint on Column.
9. What are composite constraints? 

Note: In the next article, I am going to discuss Primary Key and Foreign Key Constraint.

What is Data Integrity in SQL Server?

Data integrity means the data contained in the database is accurate, consistent, and reliable. To provide data integrity, RDBMS provides us a set of integrity constraints that ensures that the data entered into the database is going to be accurate, consistent, and reliable. This is the reason why the end-user can trust the data.

What is a Constraint in SQL Server?

We can define the SQL Server Constraint as a property that can be assigned to a column or columns of a table. The SQL Server Constraints are mainly used to maintain data integrity.

Why do we need SQL Server Constraints?

The SQL Server Constraints are used to restrict the insertion of unwanted data in any columns i.e. they are mainly used to maintain data integrity. We can create the constraint on single or multiple columns of a table in SQL Server.

What are the different types of SQL Server Constraints available?

SQL Server supports six types of constraints for maintaining data integrity. They are as follows

1. Default Constraint
2. UNIQUE KEY constraint
3. NOT NULL constraint
4. CHECK KEY constraint
5. PRIMARY KEY constraint
6. FOREIGN KEY constraint.

Note: Constraints are imposed on columns of a table.

Before going to understand the constraints in SQL Server, first, we need to understand NULL in SQL Server.

Understanding NULL in SQL Server:

NULL represents the absence of data or value. It’s neither ZERO nor EMPTY. In order to understand this, let us create the following Employee table. Here, EmployeeID is an int data type and it is not null. On the other hand, Name and Code fields are NULL types means they accept a NULL value.

Once you create the above Employee table, let us add some data as shown below.

As you can see for employee id 2, the code is 0 and for employee id 3, the name is empty and the code is 0. Here, zero and empty do not represent NULL, You need to consider them as values.

Whenever you don’t have any value, then you need to put it as Null. For example, let say we have an employee whose code is not yet generated and supposed to come in later. Then, in that case, you can put a NULL in the code as shown below which means the code is absent at the moment.

Note: When you need to tell that the data is not present or absent, then you need to put NULL. The short-cut key to put NULL is (CTRL + 0).

Once we understand what exactly NULL is, now let us proceed to constraints.

Default Constraint in SQL Server

The Default constraint in SQL Server is used to fill the column with a default value that is defined during the creation of a table if the user does not supply any value while inserting the data. In simple words, we can say that Default constraints enable the SQL Server to insert a default value to a column when the user doesn’t specify a value.

Note: IDENTITY and timestamp columns can’t be associated with the default constraint.

Let us understand Default Constraint with an example. In order to understand this, first, create the Employee table by executing the following SQL Script. 

As you can see in the above create table statement, here, we created the City, DateOfBirth, and Salary columns with the Default Constraint. So, while inserting the data into the Employee table, if the user does not specify the values for City, DateOfBirth, and Salary columns, then the default values we specified here are going to be stored in the store City, DateOfBirth, and Salary columns.

Let’s insert a record into the Employee table by executing the following Insert statement.

Insert into Employee (ID, LastName, Age) values(1, ‘Rout’, 30)

As you can see, in the above insert statement, we have not specified the values for the City, DateOfBirth, and Salary columns, so they should the default values. In order to make sure, it takes the default values, let issue a select query as shown below.

Select * from Employee

Once you execute the above Select query. It gives us the below output and you can see the City, DateOfBirth, and Salary columns are filled with the default values.

NOT NULL Constraint in SQL Server:

When you want a column not to accept NULL then you need to apply the NOT NULL constraint to that column. That means this constraint is used to avoid NULL values but it accepted duplicate values into a column.

A table can contain any number of NOT NULL Constraints. We can apply the NOT NULL constraint on any data type column such as integer, character, money, etc.

Let us understand this with an example. To understand this concept, first, let us create the Customer table by executing the following SQL Script. As you can see, we applied the NOT NULL constraint to all three fields of the Customer table.

Once you create the above Customer table, now try to execute the following SQL statements. The first, two statements are executed as expected as they don’t have any null values.

But when you try to execute the third insert statement, it will give you the below error.

Note: When we INSERT a null value into a column on which the NOT NULL constraint is imposed. The execution of the insert statement is terminated by displaying a user-friendly message telling the reason for termination and also specifies the database, the table and the column where the problem got occurred.

UNIQUE Constraint in SQL Server:

When you want a column or columns not to accept any duplicate values, then you need to apply UNIQUE Constraint to that column or columns. That means the UNIQUE constraint is used to avoid duplicate values but it accepts a single NULL value in that column.

A table can contain any number of UNIQUE constraints. We can apply the UNIQUE constraint on any data type column such as integer, character, money, etc.

Syntax: CREATE TABLE<TABLE NAME>(<COLUMN NAME><DATA TYPE><CONSTRAINTKEY>……)

Let us understand Unique Constraint with an example. In order to understand Unique Constraint, first, create the Customer table by executing the following SQL Script. As you can see, here, we applied the UNIQUE constraint to all three columns. It means none of the columns is going to accept the duplicate value.

Once you created the above table, now try to execute the below Insert statements.

INSERT customer  VALUES (10,  ‘AA’,  ‘pranayakumar777@gmail.com’)

When we execute the above statement, it executed as expected. Now, try to execute the below SQL Statement.

INSERT customer  VALUES (10,  ‘AA’,  ‘pranayakumar777@gmail.com’)

When we try to execute the above statement we will get the following error. This is because we are trying to insert duplicate values into the columns.

Again try to execute the below SQL Statements.

When we execute the above query, the third insert statement will give us the below error

If we insert a duplicate value into a column that contains UNIQUE constraint, the statements get terminated by displaying an error message but that error message will not contain the column name where the violation got occurred, it only displays the constraint name, the table name, and the value that we want to insert into the unique column as shown in the above error message.

Note: UNIQUE constraint will accept only 1 NULL value.

UNIQUE KEY, PRIMARY KEY, CHECK, and FOREIGN KEY in SQL Server:

UNIQUE KEY, PRIMARY KEY, CHECK, and FOREIGN KEY Constraints are independent objects under the database on which they are created and linked with the column of the table. That means they have their own identity or name. This is the reason why if these four constraints are violated they will never tell the column where the violation occurred. They only display the constraint name, table name, and the value that we want to insert.

Actually, you should define the constraint name while creating the table. If you have not specified the constraint name while creating the table, then the SQL server will give a random name to that constraint which is not user-friendly. So it is always advisable to provide your own constraint name while creating the table for easy identification. The naming convention something like below.

<columnname>_<constraint type>

Creating Constraint with own name:

Let us understand how to provide our own constraint name with an example. In order to understand this, please execute the below SQL Script which will create the Customer table. If you further notice, here we created two unique constraints by giving our own names such as cid_unique and email_unique.

In the above create table statement we are explicitly providing the name for the constraints. Once you created the table you can find the constraint in the keys folder as shown below

Imposing Constraint in SQL Server:

We can impose constraints on a table in two different ways

1. Imposing constraints on Column level
2. Imposing constraints on Table level

In the first case, we need to provide the constraint information inside the column only whereas in the second case we first define all the columns, and then we need to define the constraints on the columns.

Note: We cannot impose NOT NULL constraint in table level. It is possible only for the rest of the four constraints.

Imposing Constraint on Column Level:

As you can see in the following Create Table statement, we have specified the cid_unique and email_unique unique constraints directly at the column level i.e. next to the column name.

Imposing Constraint on Table Level:

As you can see in the following Create Table statement, first, we created all the columns and then we created the cid_unique and email_unique unique constraints and while creating the constraint we explicitly specified the column name to which the constraint belongs.

Note: There is no difference in behavior whether the constraint is imposed at the table level or at the column level but if a constraint is imposed at the table level, then we have the advantage of imposing composite constraints.

What are Composite Constraints in SQL Server?

Whenever a constraint is created based on more than one column then it is called Composite Constraints in SQL Server. Let us understand the composite constraint with an example.

As you can see in the following Create table statement, we have imposed the city_bc_unique unique constraint at the table level and here we specified the City and Branchcode column. That means this constraint is a composite constraint as it is created based on two columns (City and Branchcode).

Now insert some data to the BranchDetails table by executing the following insert statements.

When we execute the third statement,  it will give us the following error. This is because the combination of xyz and pqr in the column Branchcode and Branchlocation already exists and is considered as duplicate values.

Note: The drawback with a NOT NULL constraint is it will allow duplicate values whereas in the case of a UNIQUE constraint it allows null values.

Check Constraint in SQL Server:

The Check Constraint is used to enforce domain integrity. Domain integrity ensures that the values going to store in a column must follow some defined rules such as range, type, and format.

In simple words, we can say that the Domain Integrity constraint enforces the valid entries for a given column value by restricting the type of the value, the format of the data, or the range of possible values. Let’s understand this with an example.

We need to use the Check Constraints in the SQL server to limit the range of possible values of a column.

The check constraints can be created at two different levels

1. Column-Level Check Constraints: When we create the check constraints at the column level then they are applied only to that column of the table.
2. Table-level Check Constraints: When we create the check constraints at the table level, then it can be referred from any column within that table.

A table can contain any number of check constraints and will apply to any column data type like integer, character, and decimal, date, etc.

Example: Let’s insert some data to understand this concept

When we execute the above query it will give us the below error

It will give us the below error when we execute the above statement

When we execute the above query it will give us the below error

In the next article, I am going to discuss Primary Key constraints in SQL Server with examples. Here, in this article, I try to explain Constraints in SQL Server with examples. We discussed SQL server Constraints such as Default, Not Null, Unique, and Check Constraint in this article.