SQL CHECK Constraint
SQL CHECK constraint enforces a condition to check the value to be entered into a table. If you will define a CHECK constraint on a column then at the time of entering a record it evaluates that condition and if that condition is true, it accepts it to insert else if it is false then that record is rejected with error message.
You can specify CHECK constraint during table creation as well, as shown below. Here we are creating a table NewStudent and keeping a CHECK constraint on RollNumber column where it should be more than 999. So if you will try to insert RollNumber less than 1000, it will not allow as condition is not satisfied.
CREATE TABLE NewStudent ( RollNumber int NOT NULL CHECK (RollNumber > 999), Title varchar(8), FirstName varchar(50), MiddleName varchar(50), LastName varchar(50) NOT NULL, Country varchar(50), FEES int NOT NULL )
Now we will try to insert record into NewStudent table with RollNumber 900 and will try to find the error which CHECK constraint returns.
INSERT INTO NewStudent VALUES (900, 'Mr.', 'Sameer', 'K', 'Desai', 'INDIA', 22000)
So, during insert record, CHECK constraint will evaluate RollNumber column and it will compare 900 with 999, as we can see the condition is false so it will return below error message.
Msg 547, Level 16, State 0, Line 2 The INSERT statement conflicted with the CHECK constraint "CK__NewStuden__RollN__7A672E12". The conflict occurred in database "COLLEGE", table "dbo.NewStudent", column 'RollNumber'. The statement has been terminated.
You can also use CHECK constraint on multiple columns during table creation in the following manner.
CREATE TABLE NewStudent ( RollNumber int NOT NULL, Title varchar(8), FirstName varchar(50), MiddleName varchar(50), LastName varchar(50) NOT NULL, Country varchar(50), FEES int NOT NULL, CONSTRAINT CK_NewStudent CHECK(RollNumber > 999 AND Country = 'INDIA') )
If you want to drop a constraint from a table then you have to write query as given below.
ALTER TABLE NewStudent DROP CONSTRAINT CK_NewStudent
You can add CHECK constraint on existing table by writing below query.
ALTER TABLE NewStudent ADD CHECK (RollNumber > 999)
Reference: Manzoor Siddiqui [www.SQLServerLog.com]
