Check constraint question

Last Post 27 Aug 2006 09:44 PM by GoldenGal. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

16 Aug 2006 01:57 AM

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.

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.

New Member
New Member

28 Aug 2006 04:42 PM

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
The Best SQL Server 2005 Training in the World

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.

Acceptable Use Policy