Check Constraints

SQL Server Check Constraints

Check Constraints a very important role in SQL Server as it allows data validation rules to be imposed at the SQL Server instance level itself instead of writing rules for each application.

Check Constraint enforces Domain Integrity by restricting the values that can be inserted into columns. Domain Integrity ensures that only a valid range of values are allowed to be stored in a field.

To understand the Check constraint on a table let’s take a practical example of a Scholar table as shown below.

Create table Scholar

(

Scholar_id int primary key,

Scholar_first_name varchar(50),

Scholar_last_name varchar(50),

Sex varchar(1),

Age int,

)

once created a Check Constraint on the column named Sex which can have only 2 values named M and F (i.e Male and Female).

ALTER TABLE dbo.Scholar

WITH NOCHECK ADD CONSTRAINT CC_Scholar_Sex

CHECK (Sex in ('M','F'));

Once the above Check Constraint is created, SQL Server will understand that there are only two valid values for Sex  column – namely ‘M’ and ‘F’ . No other value would be acceptable.

Let’s test this by attempting to insert records into the Scholar table:

insert Scholar

select 1,'TomReffi','Kalinga','L',32

This will cause the below error to be thrown :

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CC_Scholar_Sex". The conflict occurred in database "Estore", table "dbo.Scholar", column 'Sex'.

The statement has been terminated.

This was caused as Sex has been specified as L which is not a valid value as compared to the Check Constraint definition.

Now lets try to insert a valid set of data using the below T-SQL :

insert Scholar

select 1,'TomReffi','Kalinga','M',32

The above INSERT operation would be successful it meets the Check Constraint definition.

Now, we can place a Check Constraint on the Age column using the below T-SQL.

ALTER TABLE dbo.Scholar

WITH NOCHECK ADD CONSTRAINT CC_Scholar_Age

CHECK (Age>=25);

The above Check Constraint rule means that a Scholar age needs to be greater than or equal to 25 years.

Now lets this constraint by attempting to insert data using the below T-SQL.

insert Scholar

select 2,'TomReffi','Kalinga','M',24

This will fail as the value of age is less than or equal to 25 which is not acceptable as per the Check Constraint, retrying this will a value of 26 will succeed.

If there are multiple Check Constraints they all must be satisfied for data to be successfully added.