CHECK Constraint in SQL Server

The check constraint in SQL Server allows us to validate data values that are being inserted or updated in one or more columns. If the validation fails, then SQL Server will not insert or update the data.

Need for Check Constraint

Take a look at the following query. The Salary of an employee must not be a negative number, but still, we are able to insert a negative value.

We can stop users from inserting NULL values using NOT NULL Constraints. But we also need to stop them from inserting invalid values. This is where Check Constraint comes in handy.

We specify the validation rules in Check Constraint. SQL Server checks these rules are against the values that the user inserts or updates It either accepts or rejects them based on the outcome of the validation checks.

Creating Check Constraint

The Syntax of the Check Constraint is as follows. It starts with the keyword CHECK followed by logical_expression which must return true for the validation to succeed.

In the following query, we create a check constraint on the salary & name fields. The checks if the salary is greater than 0 and the length of the name must be greater than 15.

Inserting a row name with less than 15 char will result in an error

Inserting a row with salary in negative also result in an error.

Query succeeds only if it satisfies both conditions.

Check Constraint at Table level

You can also apply the Check Constraint at table level. The Syntax of that is as follows.

It starts with the Keyword CONSTRAINT followed by name of the constraint. Then followed by CHECK and logical expression inside the bracket. Note that [ CONSTRAINT constraint_name ] is optional.

The following query creates the check constraint without naming it.

Naming the Check Constraint

You can also name the constraint. This makes it easier to later drop the constraint of no longer needed. Note that constraint names must be unique across the database. Also, make sure you follow a proper naming convention when naming the constraint. Here we use the CK_<TableName>_<ColumnsName> naming convention.

Complex Constraint Expressions

The following check constraint example shows how you can merge two conditions into a single check constraint using an AND Operator. You can refer to the other columns in the check constraint.

NULL & Check Constraints

The Presence of NULL in a check constraint expression will force check constraint returning true.

In the following example, we try to insert null into the salary column.

Finding List of Check Constraints

You can use the following query to find out the list of all check constraints in the database.

Source

Drop the Check Constraint

Dropping a check constraint is similar to dropping any other constraint. First, find out the name of the check constant using the query from the previous section and use the Alter table Drop constraint query to delete it.

Add Check Constraint to existing Table

You can use the Alter table query to add the Check constraint to the existing table.

Rules & Limitations

  1. Expession consting of NULL values are never checked.
  2. You can use user defined functions in check constraint
  3. Check constraint can refer to columns in the same table. You cannot refer columns from other tables. (But you can create a user defined function where you can use the query)
  4. You cannot define a check constaint on View

User-Defined Functions in Check Constraint

You can use user-defined functions (UDF) in check constraints. But the functions must return a scalar value.

The MinSalary UDF below returns hard-coded values of 1000. But you can as well use a query to get the value from another table and return it.

Leave a Comment

Your email address will not be published.

Scroll to Top