0

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
1

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.

  • The idea behind the check constraint is to prevent subsequent updates to the column to an "older" date. Even with the datetype change I'm still getting an error on larger datasets or multiple inserts in quick succession of large datasets – Mazhar Sep 18 '18 at 11:50
  • I understand the purpose of this constraint. Perhaps I wasn't clear on my earlier post. My point was that it is probably un-doable to do this using a check constraint. Because of the reasons I listed under 1, 2 and 3. Hence my recommendation in my final remark to investigate alternatives instead. – Tibor Karaszi Sep 18 '18 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.