CHECKING specific values
-
Comments posted to this topic are about the item CHECKING specific values
-
Ez, Thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
This harks back to a similar post a little while back, and as a result I got this right. However, I still fail to see any logic in a process that is told "I only want you to insert values that are in the following list" and then allows one in that cannot be checked because it is "missing" ! To me that NULL should be rejected simply because it cannot pass the test in a positive fashion. You don't get into the club if you ain't got ID !!
And I accept, too, that it would better be achieved by making the column NOT NULL, but that really isn't my point.
-
Nice question,
But Not having much information on MSDN for 'NULL' behaviour and incorrect value pass to the constraint.
-
Nice question. Check constraints are a way of enforcing data integrity by design that I wish more people used. Thanks.
-
Bob JH Cullen (2/23/2016)
This harks back to a similar post a little while back, and as a result I got this right. However, I still fail to see any logic in a process that is told "I only want you to insert values that are in the following list" and then allows one in that cannot be checked because it is "missing" ! To me that NULL should be rejected simply because it cannot pass the test in a positive fashion. You don't get into the club if you ain't got ID !!And I accept, too, that it would better be achieved by making the column NOT NULL, but that really isn't my point.
I agree with this sentiment. I'm sure it made sense in some way for whoever was implementing the construct but for me its just one more bit of trivia to remember when coding SQL, theres plenty of things with SQL and T-SQL I'd do differently, but I'm not the one in the position to ship a database server.
select 1 where null in (1,2,3)-- returns no rows*shrug*
-
I am shocked at how many people (9% at time of this post) said there would be 4 rows. :w00t:
Excellent question Steve.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (2/23/2016)
I am shocked at how many people (9% at time of this post) said there would be 4 rows. :w00t:Excellent question Steve.
That is interesting, isn't it? We're currently at 60% correct. I guess the question was harder than I thought.
-
I initially selected the "four" option. Then the coffee kicked in. Whew!
-
Ed Wagner (2/23/2016)
Sean Lange (2/23/2016)
I am shocked at how many people (9% at time of this post) said there would be 4 rows. :w00t:Excellent question Steve.
That is interesting, isn't it? We're currently at 60% correct. I guess the question was harder than I thought.
Actually the 60% isn't much of a surprise as I would except many people to choose 2 or error. But 4 is just shocking. That indicates that nearly 1 in 10 people that frequent the QOTD don't understand the very basics of check constraints. I certainly don't claim to be some sort of expert or anything but wow!!! I feel like posting a "trick" question about how to get the current system time from t-sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (2/23/2016)
Ed Wagner (2/23/2016)
Sean Lange (2/23/2016)
I am shocked at how many people (9% at time of this post) said there would be 4 rows. :w00t:Excellent question Steve.
That is interesting, isn't it? We're currently at 60% correct. I guess the question was harder than I thought.
Actually the 60% isn't much of a surprise as I would except many people to choose 2 or error. But 4 is just shocking. That indicates that nearly 1 in 10 people that frequent the QOTD don't understand the very basics of check constraints. I certainly don't claim to be some sort of expert or anything but wow!!! I feel like posting a "trick" question about how to get the current system time from t-sql.
I was going to select 4, but then I double checked the values used in the constraints and the inserts. I realized that the third value was different. Maybe others made the same assumption and didn't stop to double check.
-
Luis Cazares (2/23/2016)
Sean Lange (2/23/2016)
Ed Wagner (2/23/2016)
Sean Lange (2/23/2016)
I am shocked at how many people (9% at time of this post) said there would be 4 rows. :w00t:Excellent question Steve.
That is interesting, isn't it? We're currently at 60% correct. I guess the question was harder than I thought.
Actually the 60% isn't much of a surprise as I would except many people to choose 2 or error. But 4 is just shocking. That indicates that nearly 1 in 10 people that frequent the QOTD don't understand the very basics of check constraints. I certainly don't claim to be some sort of expert or anything but wow!!! I feel like posting a "trick" question about how to get the current system time from t-sql.
I was going to select 4, but then I double checked the values used in the constraints and the inserts. I realized that the third value was different. Maybe others made the same assumption and didn't stop to double check.
I guess I can see that too. Especially if not enough coffee yet. You see the constraint and the NULL in the insert so immediately realize that one will be inserted which could be what the author was looking for. I know I have made that mistake enough times with QOTD.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
So does CASE not matter here? All of the constraints were ALL CAPS but the inserts used TitleCase.
-
IowaDave (2/23/2016)
So does CASE not matter here? All of the constraints were ALL CAPS but the inserts used TitleCase.It would if you were using a case sensitive collation. But the QOTD always assumes the default and the default collation is case insensitive.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Bob JH Cullen (2/23/2016)
This harks back to a similar post a little while back, and as a result I got this right. However, I still fail to see any logic in a process that is told "I only want you to insert values that are in the following list" and then allows one in that cannot be checked because it is "missing" ! To me that NULL should be rejected simply because it cannot pass the test in a positive fashion. You don't get into the club if you ain't got ID !!And I accept, too, that it would better be achieved by making the column NOT NULL, but that really isn't my point.
So what is your point? A check constraint as you describe would effectively make the column NOT NULL, so it doesn't make sense to allow a NULLable column with your kind of constraint.
The check constraint process isn't "I only want you to insert values that are in the following list", but "I want you to refuse values that fail this test". NULL is not a value, therefore it's not tested.
The NOT NULL attribute of a column is the way to prevent NULLs from getting in. Adding that restriction to check constraints as well would remove flexibility and not add any functionality.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply