Check Constraint in SQL Server -- Part 1

In this article I would like to explain about “Constraints”. Constraints are one of the key factor in designing a table. Before I explain what is the use of constraints lets take one real world example. When you are opening a bank account ,there is a bank rule that says, you need to maintain a minimum balance of 1000 in your account. This is a business rule. There are several places to implement this rule. We can implement this in Front-End application or in the Back-end code.

The way I see is, implementing this in Back-end gives more flexibility. For now I will explain about how to implement this business rule in back-end.

There are total six types of constraints in SQL Server to implement different types of business rules and they are:
1) Check Constraints.
2) Rules
3) Default Constraints
4) Unique Constraints
5) Primary Key Constraints.
6) Foreign Key Constraints.

Other Relevant articles related to Constraints:
.. List all the Primary Keys in SQL Server Database
.. List all the Foreign Keys in a SQL Server
.. Foreign Key and Primary Key in SQL Server

In my previous articles we did talk about Default, Primary and Foreign Key Constraints. In this article we will talk about Check Constraint.

Check Constraints:

We can use check constraints to limit the range of possible values in a column or to enforce a specific pattern for data. All these check constraints must evaluate to Boolean and it can’t reference columns in another table. Basically Check constraints are table specific.

We can create check constraints at two different levels:
1) Column Level
2) Table Level

As the name implies Column level can’t reference any other column with in the same table. The best example would be Minimum balance of an account. Table Level constraints can refer any column with in the table.

Column Level

Let’s create one ACCOUNT table and insert following records.

CREATE TABLE ACCOUNT(ACCOUNT_ID INT,ACCOUNT_LOCATION VARCHAR(10),CUSTOMER_ID INT,ACCOUNT_BALANCE INT);

INSERT INTO ACCOUNT VALUES (10001,'NY',100010,50000);
INSERT INTO ACCOUNT VALUES (10002,'NJ',100020,60000);
INSERT INTO ACCOUNT VALUES (10003,'LA',100020,90000);
INSERT INTO ACCOUNT VALUES (10004,'TX',100040,15000);

We can create check constraint using CREATE/ALTER TABLE statement.

ALTER TABLE:

Lets add check constraint on ACCOUNT_BALANCE

ALTER TABLE ACCOUNT
ADD CONSTRAINT cc_MinBal
CHECK (ACCOUNT_BALANCE > 1000);

Now lets try to insert the data into this table with ACCOUNT_BALANCE = 900

INSERT INTO ACCOUNT VALUES (10005,'FL',100050,900);

When we run the above statement we will get below error.

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "cc_MinBal". The conflict occurred in database "testdemo", table "dbo.ACCOUNT", column 'ACCOUNT_BALANCE'.
The statement has been terminated.

Lets drop the Constraint

ALTER TABLE ACCOUNT DROP CONSTRAINT cc_MinBal;

Lets insert below record.

INSERT INTO ACCOUNT VALUES (10005,'FL',100050,900);


Now try to palce the check constraint on the column.

ALTER TABLE ACCOUNT
ADD CONSTRAINT cc_MinBal
CHECK (ACCOUNT_BALANCE > 1000);

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "cc_MinBal". The conflict occurred in database "testdemo", table "dbo.ACCOUNT", column 'ACCOUNT_BALANCE'.

So when you are placing a check constraint on a column it will validate the data first and then only puts the rule on that column.



CREATE TABLE:

CREATE TABLE ACCOUNT(ACCOUNT_ID INT,ACCOUNT_LOCATION VARCHAR(10),
CUSTOMER_ID INT,ACCOUNT_BALANCE INT CHECK(ACCOUNT_BALANCE > 1000) )


Check out next article on placing Check constraint at the table level.