What is Check Constraints

What is Check Constraints

Check constraints are used on database tables to maintain data consistency.

Check Constraints types

  1.  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')

0 Comments
Leave A Comment

Please login to post your valuable comments.

Join the newsletter

Get the latest vLemonn news first

share