Constraints in SQL Server  

The main job of a constraint is to enforce a rule in the database.There are four types of constraints in SQL Server –

1. Unique Constraint - A unique constraint is used to ensure a column (or a set of columns) contains no duplicate values. It allows one null value in a column.

Example – CREATE TABLE Employees (     EmployeeID int PRIMARY KEY,     EmployeeName nvarchar (40),     Age int,     CONSTRAINT UC_EmployeeName

UNIQUE(EmployeeName) )  

2. Check Constraint - Check constraints are an expression which the database evaluates when you modify or insert a row. If the expression evaluates to false, the

database will not save the row. Example – CREATE TABLE Employees (     EmployeeID int PRIMARY KEY,     EmployeeName nvarchar (40),     Age int,     CONSTRAINT

CK_Age CHECK(Age>18) )  

3. NOT NULL Constraint - The decision to allow NULL values in a column or not is governed by NOT NULL constraint.

Example – CREATE TABLE Employees      (EmployeeID int PRIMARY KEY,     EmployeeName nvarchar (40),     Age int NOT NULL,     CONSTRAINT CK_Age

CHECK(Age>18) )

4. Default Constraint - Default constraint is used to apply a default value to a column when an INSERT statement does not specify the value for the column.

Example – CREATE TABLE Employees (     EmployeeID int PRIMARY KEY,     EmployeeName nvarchar (40),     Age int NOT NULL DEFAULT(19),     CONSTRAINT

CK_Age CHECK(Age>18) )