SQL Server 2005 – How to enable Check constraint

Check constraint is basically used to enforce domain integrity on the column of the table in sql server 2005. Using this we can restrict to the user for incorrect entries.
Let’s dig out more about check constraint using below script. Here we are creating table on which check constraint is applied on price column in the table.

Condition 1 : if the price value is less than 50, system should not allow that entry into table.

Condition 2 : if price value is more than 100 then also it should allow that entry into table.

CREATE TABLE PriceHike

(price money CHECK (price >= 50 and price < 100 AND Petrol_price IS NOT NULL), Comments VARCHAR(20))

Now if you execute below query it will check whether the price value is in between 50 to 100.

INSERT INTO PriceHike (price, Comments) VALUES (55, ‘2011’)

INSERT INTO PriceHike (price, Comments) VALUES (70, ‘2011’)

When we executed the below query, while executing the same got the below error.

INSERT INTO PriceHike (price, Comments) VALUES (100, ‘2011’)

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint “CK__ PriceHike__col1__61F08603”. The conflict occurred in database “master”, table “dbo. PriceHike”, column ‘col1’.
The statement has been terminated.

Leave a Reply

  

  

  


7 − 6 =