Constraints – SQL Server

SQL Server constraints are very useful and help us to achieve data integrity. There are two types of constraints.

  1. Default Constraints
  2. Table Constraints
Constraints T-SQL

Default Constraint

It’s a column level constraint. It helps to set a default value for a column if the value for that column is not provided while insertion of the row. It makes very good sense to use default constraints with BIT type columns.

Creating a default constraint is a piece of cake. You can define it while creating a table definition. For example, in a table called Persons, the default constraint can be set for the column isVerfied as given below.

isVerified BIT NOT NULL DEFAULT 0

In above-given syntax SQL Server provides a default name for the default constraint. If you want to use a specific name which is good practice then you can use the following syntax.

isVerified BIT NOT NULL CONSTRAINT DF_TableName_ColumnName DEFAULT 0

Table Constraints

Table constraints are very useful to gain data integrity. There are two types of constraints under this category.

  1. Check Constraint
  2. Unique Constraint

Check Constraint

The Check constraint helps us to validate data while rows insert and update operation. If the values provided are not valid then the insert or update operation is rejected.

There are some rules for CHECK constraints which are given below.

  1. It can be applied to more than one columns or for just one column.
  2. We can use multiple CHECK constraints for a single column.
  3. As long as an expression return TRUE or FALSE, any valid T-SQL expression can be used in a CHECK constraint.

We can create table-level CHECK constraint by adding a constraint definition after defining all columns of the table. For example, the table level CHECK constraint for the FirstName and LastName columns can be defined as given below.

CREATE TABLE dbo.Persons
(
    ....,
    CONSTRAINT CK_Persons_FirstNameLastName CHECK (FirstName != '' AND LastName != '')
);

The above-mentioned constraint defines that the value for the FirstName and LastName columns should not be the empty string.

To create column-level CHECK constraint use the single column in the CHECK constraint expression. For example

CREATE TABLE dbo.Persons
(
    ....,
    CONSTRAINT CK_Persons_FirstNameLastName CHECK (FirstName != '')
);

Unique Constraint

The UNIQUE constraint helps to achieve unique values in a column or a combination of columns. i.e. in each row, the value of a column or combination of columns will remain unique.

For example, let say we want to store a mobile number for a person in persons table. The mobile number column should have unique values. We can achieve this by adding the UNIQUE constraint on the mobile number column using the below-given syntax.

CREATE TABLE dbo.Persons
(
    ....,
    CONSTRAINT UQ_Persons_MobileNumber UNIQUE (MobileNumber)
);

NOTE: UNIQUE constraints are actually indexes as well.