CHECK Constraints vs NULL values

  • jclementz

    SSC Eights!

    Points: 910

    Comments posted to this topic are about the item CHECK Constraints vs NULL values

  • Mr. Kapsicum

    SSCertifiable

    Points: 6128

    Good Learning for the day

    Thanks

  • Toreador

    SSChampion

    Points: 11257

    I got it right, but on the basis that the table would be empty due to the invalid date formats used. Should have remembered the US-centric nature of this site!

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286982

  • Mighty

    SSCrazy Eights

    Points: 8813

    Good question. Learned something here.

    Expected the UNKNOWN to result in a constraint violation.

  • John Hanrahan

    Hall of Fame

    Points: 3825

    I got it right but it was through ignorance. We don't use constraints much and I had never heard of is_not_trusted. Good info.

  • Revenant

    SSC-Forever

    Points: 42467

    A nice one for this Friday. Thanks, Justin!

  • Ken Wymore

    SSCoach

    Points: 16612

    Nice question. Thanks Justin.

  • webrunner

    SSC-Dedicated

    Points: 30303

    Good one, thanks. I got it wrong but learned something.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • TomThomson

    SSC Guru

    Points: 104773

    Nice question illustrating the effect of NULLs on CHECK constraints, which I suspect confuses many people. Not surprising that 60% of answers so far have it wrong.

    Looking for FALSE and treating UNKOWN as TRUE in a CHECK constraint is sort of reasonable because if one looks for TRUE and treats UNKNOWN as false the CHECK constraint effectively enforces a NOT NULL constraint on each column referenced in the CHECK constraint and there's already a perfectly good way of doing that (specifying NOT NULL in the column definitions). Effectively treating UNKNOWN as TRUE is the only viable way of making CHECK constraints apply to nullable columns. But it bewildered me the first time I came across it until I had done some hard thinking.

    Tom

  • twin.devil

    SSC-Insane

    Points: 22208

    good thoughtful question. really nice share. Thanks

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6408

    So is this what is needed?

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

    GO

    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

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the question.

Viewing 15 posts - 1 through 15 (of 20 total)

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