Sql-server – Error on Check Contraint that is on a Column with a Default Constraint

check-constraintsconstraintsql serversql-server-2016

Inspired by this article, I wanted to test the idea before I considered implementing it.

However, I get this error

Msg 547, Level 16, State 0, Line 14 The INSERT statement conflicted
with the CHECK constraint "CK__DateCreated2". The conflict occurred in
database "db_name", table "dbo.t2", column 'DateCreated'. The
statement has been terminated.

OK, i know what the error means but how do I get around it.

Here's my sample data with DDL

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
     Id             INT IDENTITY
    ,Col1           NVARCHAR (15)
    ,DateCreated    DATETIME2 CONSTRAINT DEF__DateCreated2 DEFAULT (GETUTCDATE())
);
ALTER TABLE t2 WITH CHECK ADD CONSTRAINT CK__DateCreated2 CHECK ( DateCreated = GETUTCDATE()) ;

This INSERT results in the error

;WITH Numbers (Number) AS
(   SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N1(N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N2 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N3 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N4 (N)
)
INSERT INTO dbo.t2
(Col1,DateCreated)
SELECT 'Col' + CONVERT(NVARCHAR(50), M.Number), GETUTCDATE() 
FROM Numbers M

so does this

;WITH Numbers (Number) AS
(   SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N1(N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N2 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N3 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N4 (N)
)
INSERT INTO dbo.t2
(Col1)
SELECT 'Col' + CONVERT(NVARCHAR(50), M.Number) FROM Numbers M

and this

DECLARE @UTCNow DATETIME2 = GETUTCDATE() ;

;WITH Numbers (Number) AS
(   SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N1(N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N2 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N3 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N4 (N)
)
INSERT INTO dbo.t2
(Col1,DateCreated)
SELECT 'Col' + CONVERT(NVARCHAR(50), M.Number), @UTCNow
FROM Numbers M

Best Answer

To be honest, I think you are in for a rough ride here. I see several issues with how this will pan out.

  1. The SELECT (the subselect for the INSERT) will resolve GETUTCDATE to something which later will be used in the INSERT part. The CHECK constraint is evaluated row by row, and since the value from GETUTCDATE will be "old", then you get the error.

  2. I can't say why this fails for sure, but I'm speculating that the default value it captured momentarily a very short time before the check constraint is evaluated. Again, not the same value and hence the error.

  3. Here it is pretty obvious that you first capture the date into a variable, which at time of INSERT will be old.

I changed the data type to datetime2(0) and the check constraint to CHECK ( DateCreated = CAST(GETUTCDATE() AS datetime2(0)) and I didn't get any errors. But who knows if you happen to pass a one-second boundary and it then will fail. Also, that resolution might not cut it for you.

Another issue that I see that that a subsequent UPDATE of a row might re-trigger that check constraint to be evaluated (even if you don't modify that column) and that UPDATE will fail even though the data is "correct".

I would investigate either protect from UPDATE using permissions or having a trigger.

Related Question