Check Constraint limits the values that can be entered into a column.
Check Constraint works on Row basis. Allow Null Values to be inserted.
In the following script, we create a table named CheckConstraintTable and a check constraint named CheckConstraintOrnek.
The Check Constraint forces the values entered for column 1 to be greater than 2 and the values entered for column 2 to be smaller than 5.
|
1 2 3 4 |
CREATE TABLE CheckConstraintTablo (kolon1 int, kolon2 int); GO ALTER TABLE CheckConstraintTablo ADD CONSTRAINT CheckConstraintOrnek CHECK (kolon1>2 AND kolon2 <5 ); |
After Create the Check Constraint, let’s write two insert statements, one to be successful and the other to fail.
|
1 2 3 4 5 |
USE [Test] GO INSERT INTO [dbo].[CheckConstraintTablo]([kolon1],[kolon2])VALUES(3,4) GO INSERT INTO [dbo].[CheckConstraintTablo]([kolon1],[kolon2])VALUES(1,4) |
The first insert in the script above provides values for column1 and column2 in the check constraint.
Therefore, the insert operation will be successful.
The second insert provides the desired value for column 2.
But the insert will not occur as it does not provide the desired value for column 1.
When we run the query, the first insert is done by saying “1 rows affected”.
But the second insert did not occur and the following error returned.
The INSERT statement conflicted with the CHECK constraint…
As another example of CHECK constraint, you can force it to enter only digits as follows.
If the first person to create the table does not create a column as an int and you want to enter only int values into that column, it might useful.
|
1 2 |
ALTER TABLE CheckConstraintTablo ADD CONSTRAINT CheckConstraintOrnek CHECK(kolon1 LIKE '%[0-9]%'); |
You can increase these examples. I will not go into more detail on this article.
You can delete the CHECK Constraint from the database as follows.
If you want to delete with the code, you can also delete it as follows.
|
1 |
ALTER TABLE [dbo].[CheckConstraintTablo] DROP CONSTRAINT [CheckConstraintOrnek] |
