Constraints in SQL Server

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 details.

  1. What is Data Integrity?
  2. What is SQL Server constraints?
  3. Why do we need SQL Server constraints?
  4. Different type of constraint in SQL Server?
  5. What is Default, Not Null, Unique and Check Constraint in SQL Server?
  6. How to Impose Constraint on Table.
  7. Imposing Constraint on Column.
  8. 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 a set of integrity constraints which ensures that the data entered into the database is accurate, valid and consistent.

What is a Constraint in SQL Server?

We can define the SQL Server Constraint as a property which can be assigned to a column or columns of a table. The SQL Server Constraints are mainly used to maintain data integrity. That means it ensures that the data going to store in a database is valid, consistent and accurate.

Why do we need SQL Server Constraints?
  1. The SQL Server Constraints are used to restrict the insertion of unwanted data in any columns.
  2. We can create constraints on single or multiple columns of any table.
  3. It maintains the data integrity i.e. accurate data or original data of the table.
What are the different types of SQL Server Constraints available?

SQL Server supports six types of constraints for maintaining data integrity those are

  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.

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 word, we can say that Default constraints enable the SQL Server to write default value to a column when the user doesn’t specify a value.

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

Let us see an example for understanding this concept

CREATE TABLE Employee (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Mumbai',
    DateOfBirth date DEFAULT GETDATE(),
    Salary   DECIMAL (18, 2) DEFAULT 5000.00
)

In the above Employee table City, DateOfBirth and Salary columns are created with Default constraint so while we are inserting the value to the Employee table if we do not specify the values for these columns then it stores the default values.

Let’s insert a record into this table as: Insert into Employee (ID, LastName, Age) values(1, ‘Rout’, 30)

In the above example, we didn’t specify the values for the City, DateOfBirth, and Salary column so it takes the default value.

Select the data from the table as select * from Employee

SQL Server Constraints - Data in SQL

NOT NULL Constraint in SQL Server:

If the NOT NULL constraint is applied on a column then that column will not allow any null values into it. That means it 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.

Example: To understand this concept let’s create a table using NOT NULL constraint.

CREATE TABLE customer 
( 
    id    INT NOT NULL, 
    name  VARCHAR(30) NOT NULL, 
    mobno CHAR(10) NOT NULL 
) 

INSERT customer 
VALUES (101, 
    'AA', 
    '9853977973')--ALLOWED 

INSERT customer 
VALUES (101, 
    'AA', 
    '9853977973')--ALLOWED 

INSERT customer 
VALUES (NULL, 
    NULL, 
    NULL)--NOTALLOWED 

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

SQL Server Constraints

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:

If this constraint is applied on a column then that column will not allow duplicate values. That means UNIQUE constraint is used to avoid duplicate values but it accepted null values in a column.

The unique key constraints are used to enforce entity integrity as the primary key constraints.

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>……)

Example: Let’s create a table to understand this concept.

CREATE TABLE customer 
( 
    Id      INT UNIQUE, 
    NAME    VARCHAR(30) UNIQUE, 
    Emailid VARCHAR(100) UNIQUE 
) 

-- Let’s insert data into customer table
INSERT customer 
VALUES (10, 
        'AA', 
        'pranayakumar777@gmail.com')--ALLOWED 

INSERT customer 
VALUES (10, 
        'AA', 
        'pranayakumar777@gmail.com')--NOT ALLOWED 

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

SQL Server Constraints

INSERT customer 
VALUES (NULL, 
        NULL, 
        NULL)--ALLOWED 

INSERT customer 
VALUES (11, 
        'BB', 
        'pranayakumar7@gmail.com')--ALLOWED 

INSERT customer 
VALUES (NULL, 
        NULL, 
        NULL) -- NOT ALLOWED 

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

SQL Server Constraints

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.

The unique key constraint is used to ensure that there is no duplicate value in the column on which the Unique Constraint is applied. Both unique key and primary key enforces the uniqueness of a column but there is one difference between them that is the unique key constraint allows a single null value whereas primary key does not allow any null value.

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

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

Actually, we should have to define the constraint name while creating the table. If we are not defined the constraint name while creating the table, then SQL server will give a name to that constraint and those names will not be user-friendly for identification. So it is always advisable to provide your own constraint name to the constraint while creating the table for easy identification by adopting a naming convention as  <columnname>_<constraint type>

Example:
CREATE TABLE customer 
( 
    Id      INT CONSTRAINT cid_unique UNIQUE, 
    NAME    VARCHAR(30), 
    Emailid VARCHAR(100) CONSTRAINT email_unique 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

SQL Server Constraints - Constraint Location

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:
CREATE TABLE customer 
( 
    Id      INT CONSTRAINT cid_unique UNIQUE, 
    NAME    VARCHAR(30), 
    Emailid VARCHAR(100) CONSTRAINT email_unique UNIQUE 
) 
Imposing Constraint on Table Level:
CREATE TABLE customer 
( 
    Id      INT, 
    NAME    VARCHAR(30), 
    Emailid VARCHAR(100),
    CONSTRAINT cid_unique UNIQUE(Id), 
    CONSTRAINT email_unique UNIQUE(Emailid)
) 
What are Composite Constraints in SQL Server?

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 an advantage of imposing composite constraints. That is one constraint on multiple columns.

For example:

CREATE TABLE BranchDetails 
( 
    City           VARCHAR(50), 
    Branchcode     VARCHAR(10), 
    Branchlocation VARCHAR (30), 
    CONSTRAINT city_bc_unique UNIQUE(City, Branchcode) 
)

Here we created one constraint (i.e. city_bc_unique) based on two columns (i.e. City, Branchcode) so as the constraint is created on two columns we can call this constraint as a composite constraint. Now insert some test data as follows

Insert into BranchDetails (City, Branchcode, Branchlocation) values('Mumbai', 'xyz','abc') -- Allowed
Insert into BranchDetails (City, Branchcode, Branchlocation) values('Mumbai', 'abc','pqr') -- Allowed
Insert into BranchDetails (City, Branchcode, Branchlocation) values('Mumbai', 'xyz','pqr') --Not Allowed

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

SQL Server Constraints

Note: The drawback with a NOT NULL constraint is it will allow duplicate values whereas in case of a UNIQUE constraint it will allow 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 word, 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.

CREATE TABLE Employee 
( 
    Emp_id       INT NOT NULL CHECK(Emp_id BETWEEN 0 AND 1000), 
    Emp_name     VARCHAR(30) NOT NULL, 
    Entered_date DATETIME NOT NULL CHECK(Entered_date <= CURRENT_TIMESTAMP), 
    Dept_no      INT CHECK(Dept_no > 0 AND Dept_no < 100) 
) 

We need to use the Check Constraints in 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 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 on any column data type like integer, character, and decimal, date, etc.

Example: Let’s insert some data to understand this concept
INSERT INTO Employee 
            (Emp_id, 
             Emp_name, 
             Entered_date, 
             Dept_no) 
VALUES      (1, 
             'Pranaya', 
             '2018-04-28 12:18:46.813', 
             10) -- Allowed 
INSERT INTO Employee 
            (Emp_id, 
             Emp_name, 
             Entered_date, 
             Dept_no) 
VALUES      (1001, 
             'Pranaya', 
             '2018-04-28 12:18:46.813', 
             20) --Not Allowed

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

SQL Server Constraints

INSERT INTO Employee 
            (Emp_id, 
             Emp_name, 
             Entered_date, 
             Dept_no) 
VALUES      (2, 
             'Pranaya', 
             '2018-04-30 12:18:46.813', 
             10) – Not Allowed 

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

SQL Server Constraints

INSERT INTO Employee 
            (Emp_id, 
             Emp_name, 
             Entered_date, 
             Dept_no) 
VALUES      (3, 
             'Pranaya', 
             '2018-04-27 12:18:46.813', 
             101) --Not Allowed 

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

SQL Server Constraints

In the next article, I am going to discuss Primary Key Constraint 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. 

Leave a Reply

Your email address will not be published. Required fields are marked *