Check Constraints play 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 student table as shown below.
Create table student ( Student_id int primary key, Student_first_name varchar(50), Student_last_name varchar(50), Sex varchar(1), Age int, )
Now, create 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.student
WITH NOCHECK ADD CONSTRAINT CC_student_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 student table:
insert student select 1,'ABC','PQR','L',12
This will cause the below error to be thrown :
Msg 547, Level 16, State 0, Line 2 The INSERT statement conflicted with the CHECK constraint "CC_student_Sex". The conflict occurred in database "student", table "dbo.student", 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 student select 1,'ABC','PQR','M',12
The above INSERT operation would be successful as 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.student WITH NOCHECK ADD CONSTRAINT CC_student_Age CHECK (Age>=3);
The above Check Constraint rule means that a student age needs to be greater than or equal to 3 years.
Now lets this constraint by attempting to insert data using the below T-SQL.
insert student select 2,'ABC','PQR','M',1
This will fail as the value of age is less than or equal to 3 which is not acceptable as per the Check Constraint, retrying this will a value of 4 will succeed.
If there are multiple Check Constraints they all must be satisfied for data to be successfully added.
Please let us know in the comments if you have any feedback or suggestions, alternatively you can contact me at singhsatnam84@yahoo.com.
]]>