The CHECK constraints enforce integrity by limiting the values that are accepted by one or more columns. You can create a CHECK constraint with any logical expression that returns TRUE or FALSE based on the logical operators.
CHECK constraints are similar to FOREIGN KEY constraints in that they control the values that are put in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression. You can read more about them here.
In this article I'll show how can insertion of data be different under unlike settings for a same constraint in a Table. Three examples will be demonstrated.
Example 1
This example creates a database with all the default settings for a database creation statement. A table dbo.Orders is created with a primary key and CHECK constraint on the OrderStatus column. Then some data is inserted.
CREATE DATABASE check_db_test;
GO
USE check_db_test
GO
CREATE TABLE dbo.Orders
(
OrderID int,
OrderTotal money,
OrderStatus varchar(20)
CONSTRAINT Orders_Status_Code CHECK(OrderStatus IN( 'ACTIVE', 'INACTIVE', 'OTHER' ))
CONSTRAINT [PK_Orders] PRIMARY KEY(OrderId)
);
GO
Now I run the next batch to insert four rows.
INSERT INTO dbo.Orders SELECT 1, 435.43, 'Active';
INSERT INTO dbo.Orders SELECT 2, 554.66, 'InActive';
INSERT INTO dbo.Orders SELECT 3, 129.12, 'Not Active';
INSERT INTO dbo.Orders SELECT 4, 1228.00, NULL;
GO
What happens?
When the insert batch runs, each insert is treated separately as an implicit transaction. The first two insert rows pass the CHECK constraint and get inserted. The third insert row fails the CHECK constraint and throws an error without inserting the row.
Msg 547, Level 16, State 0, Line 17
The INSERT statement conflicted with the CHECK constraint "Orders_Status_Code". The conflict occurred in database "check_db_test", table "dbo.Orders", column 'OrderStatus'.
The fourth row does not fail the CHECK constraint, since a NULL is an unknown value, and the row is inserted.
I also want to note that only three execution plans are generated for the INSERT batch from above.
The next query lists all inserted rows.
SELECT * FROM dbo.Orders;
OrderID OrderTotal OrderStatus
----------- --------------------- --------------------
1 435.43 Active
2 554.66 InActive
4 1228.00 NULL
3 row(s) affected)
Example 2
This example overviews the case when the XACT_ABORT is set to ON.
XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
There are often situations where this setting is set to ON. It's default set up is OFF.
TRUNCATE TABLE dbo.Orders;
SET XACT_ABORT ON;
Insert the same rows.
INSERT INTO dbo.Orders SELECT 1, 435.43, 'Active';
INSERT INTO dbo.Orders SELECT 2, 554.66, 'InActive';
INSERT INTO dbo.Orders SELECT 3, 129.12, 'Not Active';
INSERT INTO dbo.Orders SELECT 4, 1228.00, NULL;
GO
(1 row(s) affected)
(1 row(s) affected)
On the third try to insert an row the transaction fails and the whole batch exists the execution. The third (implicit) transaction is rollbacked. Next is the message.
Msg 547, Level 16, State 0, Line 14
The INSERT statement conflicted with the CHECK constraint "Orders_Status_Code".
The conflict occurred in database " check_db_test", table "dbo.Orders", column 'OrderStatus'.
SELECT * FROM dbo.Orders;
OrderID OrderTotal OrderStatus
----------- --------------------- --------------------
1 435.43 Active
2 554.66 InActive
(2 row(s) affected)
Two rows are inserted, instead of the expecting three. That is because of the XACT_ABORT setting to ON.
Example 3
This example shows how the database COLLATION can change the behaviour of a constraint. For that purpose I'm creating another database with Case Sensitive (CS) collation. Make sure that you're doing this in another query window.
CREATE DATABASE check_db_test_COLLATE COLLATE SQL_Latin1_General_CP1_CS_AS
GO
USE check_db_test_COLLATE
GO
CREATE TABLE dbo.Orders
(
OrderID int,
OrderTotal money,
OrderStatus varchar(20)
CONSTRAINT Orders_Status_Code CHECK(OrderStatus IN( 'ACTIVE', 'INACTIVE', ' Not Active' ))
CONSTRAINT [PK_Orders] PRIMARY KEY(OrderID)
);
GO
First, for the table creation I got an error for the "OrderId" column because it's different from "OrderID". Then I changed it to "OrderID".
Msg 1911, Level 16, State 1, Line 78
Column name 'OrderId' does not exist in the target table or view.
Msg 1750, Level 16, State 0, Line 78
Could not create constraint or index. See previous errors.
SET XACT_ABORT OFF; --make sure the default value is set up.
Run the same insertion of data.
INSERT INTO dbo.Orders SELECT 1, 435.43, 'Active';
INSERT INTO dbo.Orders SELECT 2, 554.66, 'InActive';
INSERT INTO dbo.Orders SELECT 3, 129.12, 'Not Active';
INSERT INTO dbo.Orders SELECT 4, 1228.00, NULL;
GO
Only one row inserted ant that is the last one.
SELECT * FROM dbo.Orders;
OrderID OrderTotal OrderStatus
----------- --------------------- --------------------
4 1228.00 NULL
(1 row(s) affected)
If the XACT_ABORT was set to ON, then none of the insert statements would pass and zero rows will be inserted.
Conclusion
From the examples we've seen how insertion of data could be different when some settings take place. If we start from Example 1 as a default set up for which we can assume that is most similar to real production environments and then going through Examples 2 and 3 which could be also present on some production environments, we can see the different results for the same data insertion.
The design of the check constraint is not an easy task as it seems at first glance. It becomes a more problematic task when the column for which the constraint is aimed accepts NULLs. NULLs are unknown or missing data and they are not caught up by constraints. Of course someone would propose the column be created with NOT NULL constraint, but it's a matter of business logic decisions and/or database design principles.
