January 28, 2016 at 11:47 am
I'm sorry, I must be very dull today, but I don't understand how any rows are inserted by the INSERT statements when the primary key is an IDENTITY(1,1), regardless of the presence, absence, or logic of the check constraint.
If the create table is changed to this, without the check constraint:
create table CheckTest
(id int identity(1,1) primary key
--, myval int constraint CheckTest_MyVal_LT_10 check( myval < 10)
)
go
then the INSERTs fail as expected (by me, anyway) as follows:
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'CheckTest' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Why does adding a check constraint bypass the rules of IDENTITY_INSERT?
Again, I'm sure I'm missing something obvious here...
Rich
January 28, 2016 at 1:35 pm
Rich, you've commented out the second column.
January 28, 2016 at 2:05 pm
Rich Mechaber (1/28/2016)
I'm sorry, I must be very dull today, but I don't understand how any rows are inserted by the INSERT statements when the primary key is an IDENTITY(1,1), regardless of the presence, absence, or logic of the check constraint.If the create table is changed to this, without the check constraint:
create table CheckTest(id int identity(1,1) primary key
--, myval int constraint CheckTest_MyVal_LT_10 check( myval < 10)
)
go
then the INSERTs fail as expected (by me, anyway) as follows:
Msg 8101, Level 16, State 1, Line 1An explicit value for the identity column in table 'CheckTest' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Why does adding a check constraint bypass the rules of IDENTITY_INSERT?
Again, I'm sure I'm missing something obvious here...
Rich
Rich, I think your confusion is coming from the default behavior of INSERT statements.
In the original question, the table has 2 columns. The first is an IDENTITY column, the second an int with a constraint. The INSERT doesn't specify the columns, and the compiler sees 1 column being presented. Knowing that the table has an IDENTITY column, which can't be manually specified in the current context, the compiler assumes this 1 column is meant to be inserted as the (second) int column, and the IDENTITY column should be defaulted. In other words, it reads "insert CheckTest select 1" as insert CheckTest (myval) select 1
In your example, with the second column commented out, the compiler sees the INSERT differently. Now, it only has 1 column, so the compiler deduces you are trying to insert the value into the only column, which is an IDENTITY column and invalid in the current context, hence the error. So this time, even though the INSERT looks the same, the compiler interprets it as insert CheckTest (id) select 1
So although the INSERT itself looks the same in both examples, the compiler interprets them differently based on the underlying table's definition.
January 28, 2016 at 2:55 pm
Steve Jones - SSC Editor (1/28/2016)
Rich, you've commented out the second column.
Second column? What second column??? :ermm:
Aaaaaand that's why I was confused. Missed that completely. Hope that's the maximum DOH moment of the week for me -- time for liquid refreshment and resuscitation!
Thanks Steve,
Rich
January 29, 2016 at 6:18 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
January 29, 2016 at 7:13 am
OK , but why is the Id incremented and Id = 3 skipped ?
Ta,
Iulian
January 29, 2016 at 8:16 am
Iulian -207023 (1/29/2016)
OK , but why is the Id incremented and Id = 3 skipped ?Ta,
Iulian
From https://msdn.microsoft.com/en-us/library/ms186775.aspx, under "Remarks":
For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again.[/i] This can result in gaps when the subsequent identity values are generated.
(emphasis mine)
Cheers!
January 29, 2016 at 9:57 am
Jacob Wilkins (1/29/2016)
Iulian -207023 (1/29/2016)
OK , but why is the Id incremented and Id = 3 skipped ?Ta,
Iulian
From https://msdn.microsoft.com/en-us/library/ms186775.aspx, under "Remarks":
For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again.[/i] This can result in gaps when the subsequent identity values are generated.
(emphasis mine)
Cheers!
I am courios if the sequence behave differently.
Iulian
January 29, 2016 at 10:22 am
Iulian -207023 (1/29/2016)
Jacob Wilkins (1/29/2016)
Iulian -207023 (1/29/2016)
OK , but why is the Id incremented and Id = 3 skipped ?Ta,
Iulian
From https://msdn.microsoft.com/en-us/library/ms186775.aspx, under "Remarks":
For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again.[/i] This can result in gaps when the subsequent identity values are generated.
(emphasis mine)
Cheers!
I am courios if the sequence behave differently.
Iulian
They don't.
Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back.
January 29, 2016 at 1:27 pm
Luis Cazares (1/29/2016)
Iulian -207023 (1/29/2016)
Jacob Wilkins (1/29/2016)
Iulian -207023 (1/29/2016)
OK , but why is the Id incremented and Id = 3 skipped ?Ta,
Iulian
From https://msdn.microsoft.com/en-us/library/ms186775.aspx, under "Remarks":
For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again.[/i] This can result in gaps when the subsequent identity values are generated.
(emphasis mine)
Cheers!
I am courios if the sequence behave differently.
Iulian
They don't.
Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back.
This clarify it.
T.a.
Iulian
January 31, 2016 at 4:51 am
Nice & eazy, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
January 31, 2016 at 11:08 pm
Easy Question...
NULL value is not under the Check Constraint...
February 1, 2016 at 1:45 am
This is yet another one of those things in SQL Server which appear counter-intuitive. To me, at least. If I want to check that any data in that column must be greater than 10 (which is the obvious way round to consider things) I would expect that the constraint, as written, should do just that, and in the process disqualify NULLs, because NULL cannot be tested for being greater than 10. On the other hand, if what I meant was that the value cannot be less than 11, then that should include NULL, as you can only tell for certain if a value is less than 11 if it is not null.
In other words, I am expecting that the value will only be allowed if the test is categorically TRUE, whereas the explanations given are saying the value will be disallowed if the test is categorically FALSE. Inverse logic and, as I said, to me counter-intuitive.
February 1, 2016 at 1:36 pm
Bob JH Cullen (2/1/2016)
If I want to check that any data in that column must be greater than 10
(Emphasis mine)
And there's the rub. A NULL is not data. A NULL is "a marker to represent the absence of any data value" (quoting the ANSI standard by head, so I may have some subtleties wrong, but the generic gist of it is defnitely that)
February 1, 2016 at 6:41 pm
Bob JH Cullen (2/1/2016)
This is yet another one of those things in SQL Server which appear counter-intuitive. To me, at least. If I want to check that any data in that column must be greater than 10 (which is the obvious way round to consider things) I would expect that the constraint, as written, should do just that, and in the process disqualify NULLs, because NULL cannot be tested for being greater than 10. On the other hand, if what I meant was that the value cannot be less than 11, then that should include NULL, as you can only tell for certain if a value is less than 11 if it is not null.In other words, I am expecting that the value will only be allowed if the test is categorically TRUE, whereas the explanations given are saying the value will be disallowed if the test is categorically FALSE. Inverse logic and, as I said, to me counter-intuitive.
The trouble is that you are thinking that 2-valued logic is relevant when there can be missing data. In real life it isn't. Even in mathematics it isn't (that's a plus for having constructive mathematics and intuitionist logic as well as the old 2-valued logic; of course in maths we take it to extremes, some mathematicians are unhappy with any finite number of truth values, they want a countable infinity of them instead of just 3 as SQL uses).
Suppose for example I have a table that has details of people in a particular class at the school, and that there's a rule that people taking that class must be over 8 years old. There's a child who is going to take the class, and we know she is at least 9 years old; but don't know whether she is 9, 10 or 11 - we don't have the age data; but we do know that she is old enough to take the class, and we do know that she is scheduled to take it, and we need to representthat information in the database. so she's going to be in the table of children for that class, and her age is going to be null. The constraint AGE > 8 can't reasonably interpreted as excluding NULL, because it would prevent us recording valid infomation. It's useful for the row to be there now so that we have an accurate count - the AGE can be filled in later when we have it.
In cases where we want to exclude NULL, we specifically bar that value, so the column is declared as
AGE INT NOT NULL CONSTRAINT MIN_AGE CHECK(AGE > 8)
instead of as
AGE INT CONSTRAINT MIN_AGE CHECK(AGE > 8).
(And please dont put tests for NULL in your check constraints when they can be avoided using the NOT NULL constraints provided by the type system, as shown above.)
Tom
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply