Check constraints are used on database tables to maintain data consistency.
Check Constraints types
- Primary Key: Used to ensure database table contains non null unique records.
/* Primary Key */
CREATE TABLE #tempEmployee1
(
EmployeeID INT,
EmployeeName VARCHAR(50),
CONSTRAINT pk_Employee_ID PRimary Key(EmployeeID)
)
2. Unique Key: Same as Primary key with the exception it will allow one null value.
/*Unique records for Employee ID */
CREATE TABLE #tempEmployee2
(
EmployeeID INT,
EmployeeName VARCHAR(50),
CONSTRAINT uk_Employee_ID UNIQUE (EmployeeID)
)
INSERT INTO #tempEmployee2
VALUES(1,'Employee1')
INSERT INTO #tempEmployee2 --Failed because of duplicate records
VALUES(1,'Employee1')
3. Null Values.
Decide whether we have to allow null values in columns.
/*Not Allow Null*/
CREATE TABLE #tempEmployee4
(
EmployeeID INT,
EmployeeName VARCHAR(50),
CONSTRAINT ck_Employee_Name_NotNull CHECK(EmployeeName IS NOT NULL )
)
INSERT INTO #tempEmployee4
VALUES(1,NULL)--Failed because of Null values
4. User Define.
To apply user defined conditions.
/*User Define Checks*/
CREATE TABLE #tempEmployee3
(
EmployeeID INT,
EmployeeName VARCHAR(50),
CONSTRAINT ck_Employee_Name_Len CHECK(LEN(EmployeeName)>5 )
)
INSERT INTO #tempEmployee3
VALUES(1,'Sri') --Failed because of Employee Name length is less than 6
INSERT INTO #tempEmployee3
VALUES(1,'SriRam')