Check Constraint in SQL Server -- Part 2

In this article I will talk about Table Level Constraints in SQL Server. These are very useful if you want to have rules based on multiple columns with in a table.

Please check my article on creating constraint at the column Level.

This can be created in two different ways i.e. CREATE TABLE/ALTER TABLE

Let’s go with an example.

Business rule which I want to set is if a person opens check-in account and savings account then minimum balance should not be less than 800 by combining both.

CREATE TABLE:

CREATE TABLE ACCOUNT(ACCOUNT_ID INT,ACCOUNT_LOCATION VARCHAR(10),
CUSTOMER_ID INT,CHECK_IN_AMT INT,SAVINGS_AMT INT,CONSTRAINT cc_min_bal CHECK(CHECK_IN_AMT+SAVINGS_AMT>=800));

If you look at the syntax of the table creation I added CHECK constraint at the end of the all the columns. This is the way to create Table level constraints.

Lets try to insert the data:

INSERT INTO ACCOUNT VALUES (10001,'NY',100010,1000,0);
INSERT INTO ACCOUNT VALUES (10002,'NJ',100020,800,0);
INSERT INTO ACCOUNT VALUES (10003,'LA',100020,0,800);
INSERT INTO ACCOUNT VALUES (10004,'TX',100040,200,200);

I was able to insert first 3 records but last INSERT statement failed because check constraint is looking for minimum of 800 by combining both the account amounts.

Error while inserting the 4th insert statement is:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "cc_min_bal". The conflict occurred in database "testdemo", table "dbo.ACCOUNT".
The statement has been terminated.

ALTER TABLE:

Now let’s define one more rule on the same table and on the same columns. The rule is Balance should not exceed more than 80000.

ALTER TABLE ACCOUNT
ADD CONSTRAINT cc_max_Bal
CHECK (CHECK_IN_AMT+SAVINGS_AMT<=80000);

Lets try to insert the data.
INSERT INTO ACCOUNT VALUES (10004,'TX',100040,200000,200);

Error while inserting this insert statement is:

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

It’s very simple isn’t it?

How do you find list of check constraints in a database defined on a table? Is there any way I can drop these constraints?? To find the answers to these questions you need to check my next article.