Sql - Column Check Constraint based on Date

Question

This isn't a duplicate of Check constraint on date but I might have missed another similar question.

On MS SQL, you can create the following constraint:

ALTER TABLE [X] WITH CHECK ADD CONSTRAINT [CCCHK03_TBX] CHECK
(
    [TBX_YEAR] = DATEPART( year, GetDate() )
)

You are able to insert records just fine but I am unable to fully test the implications of this though, what will happen when the server date ticks over to 2017? I am of the impression that it will allow inserts for 2017 but it'll theoretically invalidate all records for 2016.

This table is an insert only table so records are never meant to be updatable so that's not an issue. My main concern is whether there could potentially be a server stability issue caused by this?

I can't seem to find anything relating to this but MS must have a reason for allowing such a constraint.

Normally I'd advise creating an insert trigger and checking against that but this got me curious.

Edit: Expanded test from destination-data's answer:

IF ( OBJECT_ID( 'tempdb..#CheckTest' ) IS NOT NULL )
    DROP TABLE #CheckTest
GO

CREATE TABLE #CheckTest ( MN INT )
ALTER TABLE #CheckTest WITH CHECK ADD CONSTRAINT CHK_MN CHECK ( MN = DATEPART( SECOND, GETDATE() ) )
ALTER TABLE #CheckTest CHECK CONSTRAINT CHK_MN
GO

-- Control Test. This will fail with:
--Msg 547, Level 16, State 0, Line 12
--The INSERT statement conflicted with the CHECK constraint "CHK_MN". The conflict occurred in database "tempdb", table "dbo.#CheckTest", column 'MN'.
--The statement has been terminated.
INSERT INTO #CheckTest ( MN )
VALUES ( DATEPART( SECOND, DATEADD( SECOND, 5, GETDATE() ) ) )

-- Add 5 different seconds.
DECLARE @Counter int = 0;

WHILE @Counter < 5
BEGIN
    INSERT INTO #CheckTest ( MN )
    VALUES ( DATEPART( SECOND, GETDATE() ) )

    SET @Counter += 1;

    -- Delay for a second.
    WAITFOR DELAY '00:00:01';
END
GO

-- Add a different second.
-- Disabling and Enabling a check will work just fine so long as the check already exists.
ALTER TABLE #CheckTest NOCHECK CONSTRAINT CHK_MN;
INSERT INTO #CheckTest ( MN )
VALUES ( DATEPART( SECOND, DATEADD( SECOND, 5, GETDATE() ) ) )
ALTER TABLE #CheckTest CHECK CONSTRAINT CHK_MN;

-- Control Test. This will fail with:
--Msg 547, Level 16, State 0, Line 12
--The INSERT statement conflicted with the CHECK constraint "CHK_MN". The conflict occurred in database "tempdb", table "dbo.#CheckTest", column 'MN'.
--The statement has been terminated.
INSERT INTO #CheckTest ( MN )
VALUES ( DATEPART( SECOND, DATEADD( SECOND, 5, GETDATE() ) ) )
GO

-- Check table contents.
SELECT * FROM #CheckTest;
GO

-- Dropping and recreating the check constraint will result in an error:
--Msg 547, Level 16, State 0, Line 37
--The ALTER TABLE statement conflicted with the CHECK constraint "CHK_MN". The conflict occurred in database "tempdb", table "dbo.#CheckTest", column 'MN'.
ALTER TABLE #CheckTest DROP CONSTRAINT CHK_MN;
ALTER TABLE #CheckTest WITH CHECK ADD CONSTRAINT CHK_MN CHECK ( MN = DATEPART( SECOND, GETDATE() ) )
GO

DROP TABLE #CheckTest
GO

Edit 2: Summary

Based on the testing and feedback, while it sure was a fun exercise and definitely appears to be 100% perfectly valid, I'd definitely advise against it purely from a "future proof" point of view as the check will never be able to be altered. Personally I think the trigger based constraint would be the most maintainable.

There wont be any system instabilty when a constraint fails.Constraints are there to enforce our predefined rules — TheGameiswar, May 12 '16 at 09:11
I can't find it in the docs now, but most likely `CHECK` constraints are evaluated/validated only when a row is inserted or updated (only for the inserted or updated row). So, you can insert a row with year 2016 today and a row with year 2017 in the next year, but if in 2017 you try to update a row that was created in 2016 an update would fail. If you don't touch that old row it will remain in the table as is. — Vladimir Baranov, May 12 '16 at 11:20
@VladimirBaranov That's great, thanks for the reinforcement, that's how I was looking at it too. If that was the answer I could have marked it. — Storm, May 12 '16 at 11:23

score 3 · Answer 1 · answered May 12 '16 at 12:27

Good question!

The short answer is you will be ok. Checks are validated only when inserting or updating columns within a check. Your existing records will be unaffected by the change in year.

But there are some potential issues you should consider. This type of check can make it harder for you to amend the table design, especially via SSMS (which tends to create a new table, import the data from old, drop the old and rename the new). The import will fail as old records do not match the current constraint rules. Of course you can still amend using tSQL.

Amending Table

-- Disable check.
ALTER TABLE [Schema].[Table] NOCHECK CONSTRAINT [Check];

-- Makes changes here.

-- Enable check.
ALTER TABLE [Schema].[Table] CHECK CONSTRAINT [Check];

You do not need to disable the check when updating other columns within the table.

Test Query

Testing was a little difficult with years, so I swapped to minutes.

CREATE TABLE #CheckTest 
    (
        MN  INT CONSTRAINT CHK_MN  CHECK (MN = DATEPART(MINUTE, GETDATE()))
    )
;

-- Add two different minutes.
WHILE @COUNTER < 2 
BEGIN

    INSERT INTO #CheckTest
        (
            MN
        )
    VALUES
        (DATEPART(MINUTE, GETDATE()))
    ;

    SET @COUNTER = @COUNTER + 1;

    -- Delay for a minute.
    WAITFOR DELAY '00:01:00'
END

-- Check table contents.
SELECT
    *
FROM
    #CheckTest
;

I really like the input about disabling and enabling the check, I was sure it would fail but then I realised, that's only when trying to create the check. — Storm, May 12 '16 at 14:00

score 2 · Accepted Answer · edited May 23 '17 at 11:52

While I agree with the comment by Vladimir Baranov, I also think about the following.

Query optimizer may consider CHECK constraints, when data is queried (example), but as stated in the documentation this is only done for trusted constraints

The query optimizer does not consider constraints that are defined WITH NOCHECK.

While you created constraint specifying WITH CHECK

ALTER TABLE [X] WITH CHECK ADD CONSTRAINT [CCCHK03_TBX] CHECK ...

a year later (after some rows corresponding to next year are added) you will not be able to do

ALTER TABLE [X] WITH CHECK CHECK CONSTRAINT [CCCHK03_TBX]

Trying to do it will produce error message

Msg 547, Level 16, State 0, Line ... The ALTER TABLE statement conflicted with the CHECK constraint "CCCHK03_TBX". The conflict occurred in database "DbName", table "dbo.X", column 'TBX_YEAR'.

However in the sys.check_constraints system view

select is_not_trusted
from sys.check_constraints
where object_id = object_id('CCCHK03_TBX')

you will still see is_not_trusted = 0, where as effectively, I think, it is like is_not_trusted = 1 (because of some of the table rows no longer satisfy check expression).

I'm not sure, but I think that it may lead to situation where query optimizer will generate sub-optimal plan, unless it is smart enough to not consider CHECK constraints containing non-deterministic expressions in some circumstances (I was not able to find this information, great if someone will shed the light onto this). And I believe that query optimizer is smart enough in this circumstances to not make false judgements that may lead to producing incorrect query results.

I really like the info about the query optimiser. With regards to the enabling of the CHECK though, you only get the error if you try ADD the check, not enable it. If it's already added, you can safely enable it as per my updated test in the question. — Storm, May 12 '16 at 13:53
@Storm, disabling constraint with `alter table [T] nocheck constraint [CK]` and re-enabling it back with `alter table [T] check constraint [CK]` will leave constraint in the `is_not_trusted = 1` state (this can be checked in `sys.check_constraints`), though constraint will be enabled and later inserts/updates will respect constraint rule. Enabled does not implies trusted. The only way to make it trusted again is doing `alter table [T] WITH CHECK check constraint [CK]`. However eventually it will not be possible for this constraint. — i-one, May 12 '16 at 16:27
Your comment makes sense now, it wasn't very clear in your original answer, maybe just update your answer to elaborate a little more with the info you posted in your comment because the trust issue is a game changer. — Storm, May 13 '16 at 09:29

Sql - Column Check Constraint based on Date

2 Answers2