February 23, 2016 at 8:46 am
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.
February 23, 2016 at 9:15 am
sknox (2/23/2016)
...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.
....
Thanks - that is a perfect explanation for me. At first, I was surprised that the row with NULL is accepted. But given what you pointed out, this is the correct way for the constraint to work.
I learn something new every day here at SSC!
Thanks again.
- 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
February 23, 2016 at 9:47 am
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. 😉
Nah, not shocking. I almost picked 4. Not because of a failure to work out the constraint, but because in my haste to focus on the SQL I misread "Not Active" as "Inactive" and considered that a valid INSERT at first.
There are many different ways to be wrong!
Rich
February 23, 2016 at 10:25 am
sknox (2/23/2016)
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.
I think the part that it allows for the predicate being either true or unknown is the inconsistency that throws folks, I know it goes against my sense of programming language aesthetics, but that doesn't prevent me from using SQL 🙂
February 23, 2016 at 11:56 am
I liked that NULL twist. Thanks, Steve!
February 23, 2016 at 2:15 pm
Rich Mechaber (2/23/2016)
There are many different ways to be wrong!
Rich
Oh, so true!
February 23, 2016 at 2:30 pm
I wonder - of all the people who picked 3 and got it correct - how many thought it was the first 3 rows that worked and the NULL INSERT that failed?
February 23, 2016 at 8:55 pm
The answer is plainly wrong.
Did anyone of you guys who think "3" is a correct answer actually run the script in a batch?
The question repeats the word "batch" so many times that it must be considered as a part of the question.
The execution of the batch stops on the first fatal error - on the third insert.
Fourth insert (with NULL) never gets executed - within the batch.
The correct answer is "2".
The QoD must be corrected.
Before you start arguing this point, run this script as it is, in one go:
CREATE table Orders
( OrderID int primary key
, OrderTotal MONEY
, OrderStatus VARCHAR(20)
constraint Orders_Status_Code check( OrderStatus in ('ACTIVE', 'INACTIVE', 'TBD'))
);
GO
insert Orders select 1, 435.43, 'Active'
insert Orders select 2, 554.66, 'InActive'
insert Orders select 3, 129.12, 'Not Active'
insert Orders select 4, 1228.00, NULL
GO
SELECT * FROM dbo.Orders o
How many rows returned by the last query?
Exactly.
This is the right answer, not the one chosen by Steve.
_____________
Code for TallyGenerator
February 23, 2016 at 9:25 pm
Sergiy,
Could you share the details of the version you're using? On my laptop with 2012 SP1, I get 3 rows when running the entire script at once.
Unless you used a TRY...CATCH... block, I'm not sure that the batch should stop on the error.
February 24, 2016 at 6:07 am
"Active" <> "ACTIVE"
"Inactive" <> "Inactive"
Null is OK
1 row will be inserted
February 24, 2016 at 6:28 am
Terry.Traweek (2/24/2016)
"Active" <> "ACTIVE""Inactive" <> "Inactive"
Null is OK
1 row will be inserted
Us defaults, so collation is case insensitive.
February 24, 2016 at 6:31 am
I understand. Thanks.
February 24, 2016 at 7:13 am
Sergiy (2/23/2016)
The answer is plainly wrong.Did anyone of you guys who think "3" is a correct answer actually run the script in a batch?
The question repeats the word "batch" so many times that it must be considered as a part of the question.
The execution of the batch stops on the first fatal error - on the third insert.
Fourth insert (with NULL) never gets executed - within the batch.
The correct answer is "2".
The QoD must be corrected.
Before you start arguing this point, run this script as it is, in one go:
CREATE table Orders( OrderID int primary key
, OrderTotal MONEY
, OrderStatus VARCHAR(20)
constraint Orders_Status_Code check( OrderStatus in ('ACTIVE', 'INACTIVE', 'TBD'))
);
GO
insert Orders select 1, 435.43, 'Active'
insert Orders select 2, 554.66, 'InActive'
insert Orders select 3, 129.12, 'Not Active'
insert Orders select 4, 1228.00, NULL
GO
SELECT * FROM dbo.Orders o
How many rows returned by the last query?
Exactly.
This is the right answer, not the one chosen by Steve.
Yeah I got 3 rows with this. Active, InActive and NULL. This is what I expected but you are so adamant about this I tried it on 2005, 2008 and 2014. All three returned the same thing.
_______________________________________________________________
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/
February 24, 2016 at 8:18 am
Sean Lange (2/24/2016)
Sergiy (2/23/2016)
The answer is plainly wrong.Did anyone of you guys who think "3" is a correct answer actually run the script in a batch?
The question repeats the word "batch" so many times that it must be considered as a part of the question.
The execution of the batch stops on the first fatal error - on the third insert.
Fourth insert (with NULL) never gets executed - within the batch.
The correct answer is "2".
The QoD must be corrected.
Before you start arguing this point, run this script as it is, in one go:
CREATE table Orders( OrderID int primary key
, OrderTotal MONEY
, OrderStatus VARCHAR(20)
constraint Orders_Status_Code check( OrderStatus in ('ACTIVE', 'INACTIVE', 'TBD'))
);
GO
insert Orders select 1, 435.43, 'Active'
insert Orders select 2, 554.66, 'InActive'
insert Orders select 3, 129.12, 'Not Active'
insert Orders select 4, 1228.00, NULL
GO
SELECT * FROM dbo.Orders o
How many rows returned by the last query?
Exactly.
This is the right answer, not the one chosen by Steve.
Yeah I got 3 rows with this. Active, InActive and NULL. This is what I expected but you are so adamant about this I tried it on 2005, 2008 and 2014. All three returned the same thing.
I got 2 rows if I set xact_abort on, maybe that's his default?
February 24, 2016 at 2:50 pm
patrickmcginnis59 10839 (2/24/2016)I got 2 rows if I set xact_abort on, maybe that's his default?
Yep, spot on.
Isn't it anyone's default?
Same as ANSI_NULLS?
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply