April 30, 2015 at 11:55 pm
Comments posted to this topic are about the item CHECK Constraints vs NULL values
April 30, 2015 at 11:59 pm
Good Learning for the day
Thanks
May 1, 2015 at 2:31 am
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!
May 1, 2015 at 5:21 am
An easy way to end the week. Thanks.
May 1, 2015 at 5:26 am
Good question. Learned something here.
Expected the UNKNOWN to result in a constraint violation.
May 1, 2015 at 9:14 am
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.
May 1, 2015 at 10:18 am
A nice one for this Friday. Thanks, Justin!
May 1, 2015 at 11:05 am
Nice question. Thanks Justin.
May 1, 2015 at 3:40 pm
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
May 1, 2015 at 5:22 pm
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
May 4, 2015 at 12:02 am
good thoughtful question. really nice share. Thanks
May 4, 2015 at 1:54 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 5, 2015 at 5:02 am
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.
May 5, 2015 at 6:54 am
Thanks for the question.
May 7, 2015 at 2:52 am
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).
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy