October 17, 2011 at 7:20 am
rmechaber (10/17/2011)
Odd behavior, at least to me, as it seems the "correct" interpretation of this should be "only permit the INSERT if the constraint condition evaluates to TRUE."Anyone here know if this is an ANSI-compliant implementation? Is this how constraints work in MySQL, Oracle, DB2? Just curious....
Learned something new and got my point!
Rich
It seems that Oracle, at least, works the same way. From http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm: (bold added)
A check constraint lets you specify a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null). When Oracle evaluates a check constraint condition for a particular row, any column names in the condition refer to the column values in that row.
October 17, 2011 at 7:24 am
Good question and actually makes sense if you think about it. The check constraint is checking for values. From BOL
CHECK constraints enforce domain integrity by limiting the values that are accepted by a column.
The logic here is that the check constraint is flexible enough to allow for nulls (which the column does allow). All the check constraint evaluates is that IF there is a value it should comply to the rules established in the check constraint.
_______________________________________________________________
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/
October 17, 2011 at 7:29 am
rmechaber (10/17/2011)
Thanks for a good question. I got it right, but I thought that perhaps only one such NULL INSERT would be allowed. After reading the BOL entry and trying it out, I see that you can INSERT many such rows.Odd behavior, at least to me, as it seems the "correct" interpretation of this should be "only permit the INSERT if the constraint condition evaluates to TRUE."
Anyone here know if this is an ANSI-compliant implementation? Is this how constraints work in MySQL, Oracle, DB2? Just curious....
Learned something new and got my point!
Rich
Oops, replied to wrong post.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
October 17, 2011 at 7:32 am
Thanks for the nice easy old school T-SQL question. Perfect for a Monday.
Check Constraints have worked like this for a while.
Always bee one of those things to remember or put on a test.
Another good reason to have a default values for transactional data with constraints. Inserting an UNKNOWN value in a table instead of a defalut representation of no value is not the best design. 😎
October 17, 2011 at 7:32 am
SKYBVI (10/17/2011)
I guess the answer is wrongIt should be 1 and 2 only
beacuse only the constraint is violated,
the sql statement is terminated.
it doesn't look for other statements...
just goes out of loop.
Just wat i think this, bcoz i encountered this before..
Regards,
Sushant
Take the script provided in the question and run it in a test database. I think you'll find that all statements execute, despite the CHECK constraint error. Here are the contents of my message pane after running:
(1 row(s) affected)
(1 row(s) affected)
.Net SqlClient Data Provider: Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CHK_MyOrders_OrderAmount". The conflict occurred in database "MWC_App_PltTA", table "dbo.MyOrders", column 'OrderAmount'.
(1 row(s) affected)
(3 row(s) affected)
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
October 17, 2011 at 7:34 am
Interesting question, pretty logical when you think about it.
October 17, 2011 at 7:54 am
Sean Lange (10/17/2011)
Good question and actually makes sense if you think about it. The check constraint is checking for values. From BOLCHECK constraints enforce domain integrity by limiting the values that are accepted by a column.
The logic here is that the check constraint is flexible enough to allow for nulls (which the column does allow). All the check constraint evaluates is that IF there is a value it should comply to the rules established in the check constraint.
Thanks Sean: put that way, it makes more sense to me.
Rich
October 17, 2011 at 8:09 am
Thanks for the question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 17, 2011 at 8:49 am
Nice question, thanks!
October 17, 2011 at 8:56 am
good question. thanks!
October 17, 2011 at 9:28 am
Thanks for being kind to my brain today.
October 17, 2011 at 9:35 am
Great question. I have to keep reminding myself that a boolean can actually evaluate to one of three values: TRUE, FALSE or UNKNOWN.
Rob Schripsema
Propack, Inc.
October 17, 2011 at 11:10 am
SKYBVI (10/17/2011)
I guess the answer is wrongIt should be 1 and 2 only
beacuse only the constraint is violated,
the sql statement is terminated.
it doesn't look for other statements...
just goes out of loop.
Just wat i think this, bcoz i encountered this before..
Regards,
Sushant
Note that statements between "GO" are ran as a batch. Thus, the error will not stop the next batch from execution.
October 17, 2011 at 3:38 pm
I wonder if this works the same way without ANSI Nulls? I'm too lazy/busy to try it out.
October 18, 2011 at 5:34 am
charles evans (10/17/2011)
I wonder if this works the same way without ANSI Nulls? I'm too lazy/busy to try it out.
It works the same with SET ANSI_NULLS OFF (Deprecated feature, not supported in future version).
I believe that's because the ANSI_NULLS setting only affects tests of equality or inequality, not a test of greater/less than. My understanding is that it merely makes all NULLs equivalent, which is not how NULLs are normally treated. (See one of the many threads on this site and others for a philosophical discussion of all things NULL.)
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply