Check constraint question

Last Post 27 Aug 2006 09:44 PM by GoldenGal. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
oferfr2
New Member
New Member

--
16 Aug 2006 01:57 AM
Hi,

I wrote the expression below in a table's check constraint

(CustomerTypeID = 0 and ContentSiteID is null and ResellerID is null ) or
( CustomerTypeID = 1 and ContentSiteID is not null and ResellerID is null )

It saved it ok, but when I re-opened and viewed the expression again it was converted to:

([CustomerTypeID] = 0 and [ContentSiteID] is null and [ResellerID] is null or
[CustomerTypeID] = 1 and [ContentSiteID] is not null and [ResellerID] is null )

This is a completely different logic because
1) the OR is not prioritised above the AND.
2) It dropped the internal paranthesis.

Is there any solution or should I move my condition to the table's Update and Insert triggers?
I probably should use instead of triggers so I can cancel the insert/update if I find an error.

Thanks!
GoldenGal
New Member
New Member

--
27 Aug 2006 09:44 PM
Were you coding in SQL Server 2000 or 2005?

Did you test this check constraint as SQL Server interpreted it and did you determine that it was failing?

Obviously, I haven't tested this, so I'm thinking aloud -- AND binds tighter than OR. Multiple conditions ANDed together forms a T-SQL Search Argument, which is executed as a unit. Your check constraint (A and B and C) or (D and E and F) will be treated like (ABC or DEF). I'm wondering if, in the T-SQL world, these two check constraints -- the one you wrote and the one that SQL Server stored -- aren't the same expression. I would be very interested in finding out if the check constraint works correctly.

SQLUSA
New Member
New Member

--
28 Aug 2006 04:42 PM
Michelle,

The two expressions are logically equivalent.

When executed, AND takes precedence over OR . So dropping the parenthesis by the compiler did not cause any logic change.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/order2005/
The Best SQL Server 2005 Training in the World

GoldenGal
New Member
New Member

--
30 Aug 2006 09:15 PM
Oferfr2 -- there's your answer... what you wrote, and what SQL Server re-wrote, are the same expression. The results you get from your testing should be exactly what you're expecting.
You are not authorized to post a reply.

Acceptable Use Policy