Introduction to SQL Server Check Constraints

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.




Related Articles :

  • No Related Articles Found

4 Responses to “Introduction to SQL Server Check Constraints”

  1. it’s an useful info

  2. For SQL server like mySQL does not implement CHECK constraint, instead we can use Trigger may be AFTER INSERT.
    My question is that Constraint documentation claim that direct implementation is faster than Trigger based work around – can you elaborate that?

  3. What about use of the constraints by the Query Plan?

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |