Check Constraint in SQL Server

In this post I would like to summarize all the different articles on Check Constraints in SQL Server.

Constraints are one of the key factors in designing a table. When you are opening a bank account, bank says that you need to maintain a minimum balance of 1000 in your account. This is a business rule and this can be implemented using Check Constraints. Check Constraints can be defined at the Column level or Table Level.

Below are the list of articles related to Check Constraints:
.. Check Constraints Introduction and Defining at the Column Level
.. Defining Check Constraints at the Table Level or on multiple Columns
.. How to find Check Constraints definition and how to drop them

Check Constraint in SQL Server -- Part 3

In this article I would like to show you on how to find the check constraints defined on a table.

In my previous articles I explained about Check Constraint creation at the Table Level and also at the Column Level.

How to query Check Constraints Definition?

The below script will give you Constraint definition along with the constraint name.

SELECT TC.TABLE_NAME,CC.CONSTRAINT_NAME,CC.CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS CC,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
WHERE TC.TABLE_NAME = 'ACCOUNT'
AND TC.CONSTRAINT_TYPE = 'CHECK'
AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME



How to Drop Check Constraints?

As always to drop any object you need to find out the object name. In this case ,I want to drop cc_max_bal constraint defined on a table.

ALTER TABLE ACCOUNT DROP CONSTRAINT cc_max_bal;


That’s it. It’s very simple.

Are you looking for anything else in this!!!!

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.

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.

Rename Table in SQL Server

In this article I would like to explain about how to rename a table using SQL Server Management Studio (SSMS).

If you look at my previous article on “Load text or csv file data into SQL Server” I created a table called “Cusomer” and loaded the data from Flat file. I am supposed to create “Customer” but ended up missing “t”. I guess "T" is on a vacation during that time :)

Let’s get back to the task. When you are renaming an object you need to make sure
that no-one is using this table. When I say no-one I mean it is not referenced in any other objects like views, stored procedures and etc.

Once you are sure about this then, you can execute the sp_Rename system stored procedure.

SP_RENAME 'CUSOMER','CUSTOMER'

You just have to pass old table and new table name. Once the operation is completed you will get the below message.

Caution: Changing any part of an object name could break scripts and stored procedures.

What if the new table name already exists?? Then you will get below error message.

I tried to rename the CUSTOMER to DEPT.

Msg 15335, Level 11, State 1, Procedure sp_rename, Line 402
Error: The new name 'DEPT' is already in use as a object name and would cause a duplicate that is not permitted.

What if the table that you are trying to rename is referenced in Views? How can you find this? Is there any simple query to do it??

For all these questions please check out my next article.

Drop DEFAULT Constraint in SQL Server

In my previous article, I explained about how to assign a DEFAULT value to a column.
In this section I will explain about how to drop/remove DEFAULT value from a column.

Let’s take a step back little bit and see, what is the syntax that was used to create DEFAULT value?

ALTER TABLE DEPTADD CATEGORY VARCHAR(3) NOT NULL DEFAULT ('IT')

If you look at the syntax, I didn’t mention the name of the constraint.

But the thumb rule in SQL language is you need to have a name to drop anything!!!!!

So to drop this constraint we need to find out the name of the default constraint that was given by SQL Server.

NOTE: If you don’t provide any name to constraints, SQL Server will automatically assign one for you.

Let’s find out the name of the constraint. All the default constraints are stored in SYS.DEFAULT_CONSTRAINTS table.

Below, I listed out all the important columns from SYS.DEFAULT_CONSTRAINTS table.

SELECT NAME,OBJECT_ID,PARENT_OBJECT_ID,TYPE,
TYPE_DESC,PARENT_COLUMN_ID,DEFINITION
FROM SYS.DEFAULT_CONSTRAINTS



In my database I have only one DEFAULT constraint that’s the reason I have only one record. If you have multiple rows then how would you identify the name of the constraint?? In that case you need to resolve the PARENT_OBJECT_ID, PARENT_COLUMN_ID columns.

NOTE: When ever you create an object in SQL Server, it will automatically assign one unique number and stores that information in OBJECT_ID.

EX: SELECT NAME,OBJECT_NAME(OBJECT_ID) FROM SYS.DEFAULT_CONSTRAINTS
If you want to find out the name of the object then pass OBJECT_ID to OBJECT_NAME function.

Let’s come back to our original task. So after resolving the names using IDs the query will be

DECLARE @DEF_CONS_NAME VARCHAR(100);
SELECT @DEF_CONS_NAME = NAME
FROM SYS.DEFAULT_CONSTRAINTS
WHERE OBJECT_NAME(PARENT_OBJECT_ID) = 'DEPT'
AND upper(COL_NAME(PARENT_OBJECT_ID,PARENT_COLUMN_ID)) = 'CATEGORY'
SELECT @DEF_CONS_NAME




Now the next step is to construct the dynamic query to remove.

DECLARE @DEF_CONS_NAME VARCHAR(100);
SELECT @DEF_CONS_NAME = NAME
FROM SYS.DEFAULT_CONSTRAINTS
WHERE OBJECT_NAME(PARENT_OBJECT_ID) = 'DEPT'
AND upper(COL_NAME(PARENT_OBJECT_ID,PARENT_COLUMN_ID)) = 'CATEGORY'
exec('alter table dbo.DEPT drop constraint ' + @DEF_CONS_NAME)

That’s it. It’s very simple isn’t it?

If you closely observe the above query I used OBJECT_NAME, COL_NAME, UPPER functions. I will explain about these in my next article.