SQL Server Constraints

Constraint

Constraint is rule which is used in a table to validate date in a column. It is used to enforce domain integrity, data integrity and referential integrity etc. The rule may allow only limited range of data or unique value or a value based on business rules etc. There are different types of constraints which are given below.

Types of constraints

1. Not Null
2. Unique
3. Default
4. Check
5. Null
6. Primary key
7. Foreign key

These constrains can be applied at column or table level. On this basis, we have two types of constraints- column constraint and table constraint.

Not Null Constraint

The NOT NULL constraint is used to prevent missing value in a column or combination of columns. For example, we can force the entry of date of birth in employee table by using NOT NULL constraint upon DOB column in employee table. If user does not input the date of birth, entry will fail.

Look at the following example.

CREATE TABLE Employee
(
Id int,
DOB date NOT NULL,
Salary int
)

Insert some rows into Employee table as given below.

INSERT INTO Employee Values (1, '11/11/1999', 23000)
INSERT INTO Employee Values (2, NULL, 24000)

The first Insert statement will succeed but second one will fail because of NOT NULL constraint.

Unique Constraint

This constrain is used to prevent duplicate value in a column or combination of columns. For example, to prevent duplicate mobile number or email id in employee table, we can use unique constraint on mobile and email columns of the table. We can use null value only once in the column in case of unique constraint.

Look at the following example.

CREATE TABLE Employee
(
Id int,
Email varchar (50) UNIQUE,
Salary int
)

Insert some rows into Employee table as given below.

INSERT INTO Employee Values (1, 'appliedk@gmail.com', 23000)
INSERT INTO Employee Values (2, 'appliedk@gmail.com', 24000)

The first Insert statement will succeed but second one will fail because of UNIQUE constraint.

Table level unique constraint

We have two possible ways to apply unique constraint on more than one column in a table. Look at the following examples.

Case1. Column level

CREATE TABLE Employee
(
Id int,
Mobile varchar (20) UNIQUE,
Email varchar (50) UNIQUE,
Salary int
UNIQUE (Mobile, Email)
)

Case2. Table level

CREATE TABLE Employee
(
Id int,
Mobile varchar (20),
Email varchar (50),
Salary int
UNIQUE (Mobile, Email)
)

Both above cases are equivalent.

Default Constraint

This constrain is used to allow a default value in a column or combination of columns if user does not enter any value. For example, the current date will be automatically entered in Entry Date column in Patient table if data entry operator does not enter value for it. Look at the following example.

CREATE TABLE Patient
(
Id int,
EntryDate date DEFAULT GETDATE(),
Fee int Default 20
)

Insert some rows into Patient table as given below.

INSERT INTO Patient Values (1, '11/1/2023', 230)
INSERT INTO Patient Values (2, DEFAULT, DEFAULT)

The second Insert statement will input current date and 20 as fee.

Note that default value can be a literal or function or expression. We have used 20 literal value and GETDATE function for the default values.

Check Constraint

The check constrain is used to check rule in a column or combination of columns. For example, if the number of units ordered is less than 2 in order table, the entry will be rejected. Look at the following example.

CREATE TABLE Orders
(
Id int,
Units int check(units >= 2)
)

Insert some rows into Employee table as given below.

INSERT INTO Orders Values (1, 2)
INSERT INTO Orders Values (2, 1)

The first Insert statement will succeed but second one will fail because of CHECK constraint.

Null Constraint

The NULL constraint is by automatically applied by SQL Server but we can explicitly apply it on a column for readability. If NULL constraint is applied on a column then we can ignore entry in that column. Null constraint is applied when data entry is optional.

Look at the following example.

CREATE TABLE Employee
(
Id int,
City varchar(20) NULL,
Email varchar(50),
Salary int
)

Insert some rows into Employee table as given below.

INSERT INTO Employee(Id, Email, Salary ) Values (1, 'appliedk@gmail.com', 23000)
INSERT INTO Employee Values (2, 'Delhi', 'shri@gmail.com', 24000)

Note that we need to specify columns names if we have to ignore entry in some columns.

Primary key Constraint

The primary key constrain is used to apply unique constraint and not null constraint together on a column or some combination of columns.

1. The data cannot be null in primary key column.
2. The data must be unique in primary key column.

Look at the following example.

CREATE TABLE Student

(

Id int primary key,

Marks int

)

Insert some rows into Student table as given below.

INSERT INTO Student Values (1, 30)

INSERT INTO Student Values (1, 50)

INSERT INTO Student Values (NULL, 40)

The second and third Insert statement will fail. The second statement will fail because 1 is already entered into Id column. Third statement will fail as null value is not allowed in primary key column.

In the next post we will learn about Index and Its Types in SQL Server.