CHECK Constraints vs NULL values

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    henrik staun poulsen (5/5/2015)


    So is this what is needed?

    ALTER TABLE dbo.Test ADD CONSTRAINT CK_Test_EndDateAfterStartDateAndNotNull CHECK (EndDate > StartDate AND EndDate IS NOT NULL);

    GO

    No, if you don't want null values, you simply declare the column as NOT NULL. Hiding the equivalent of that constraint in a CHECK constraint is the kind of obfuscation that causes errors later.

    well, I could also do this:

    CREATE TABLE dbo.Test

    (

    ID INT NOT NULL,

    StartDate DATETIME NOT NULL,

    EndDate DATETIME not NULL

    );

    GO

    ALTER TABLE dbo.Test ADD CONSTRAINT CK_Test_EndDateAfterStartDate CHECK (EndDate > StartDate);

    GO

    Yes, that is how it should be done if you want rows with EndDate NULL to be rejected.

    I normally default Enddate to "9999-12-31", and do not allow Unknown dates.

    Hmmm, I recall people saying similar things in the '80s, except they had two-digit years and assumed their code would be replaced long before the date "99-12-31" would ever become real.

    I absolutely don't think your code will still be live in 7 millenia, but that does not mean that I like using magic values to replace NULL. The SQL language has included NULL as a "as good as it gets" solution to a hard problem - that of missing data. If you know how NULL works, it *usually* behaves just as you would want it to when having to deal with missing data. If you use a magic value, you will far more often have to code exceptions - a simple MAX(EndDate) will not work correct when you use 9999-12-31, and using DATEDIFF to compute remaining days can even cause runtime overflow errors.

    Also, how do you handle smalldatetime? How do you represent missing values in integer or string columns?

    (Not trying to pick on you, BTW - just trying to make a point to all the many people who go out of their way to avoid NULL instead of embracing the concept).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    we set getdate() as default value for few of our date columns (not for all date columns), but never heard of keeping such high future date.

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    Nice explanation hugo

  • Bhushan Kulkarni

    SSCrazy

    Points: 2829

    Good one, learned something new

  • simon.barnes

    Old Hand

    Points: 370

    I actually assumed that the constraint would be fine, but untrusted. And thus just learnt that new constraints issue a check upon creation.

Viewing 5 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply