problem in expression of a check constraint

Hi all,

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

(CustomerTypeID = 0 and ContentSiteID is null and ResellerID is null and AffiliateID is null and WhiteLabeLID is null ) or

( CustomerTypeID = 1 and ContentSiteID is not null and ResellerID is null and AffiliateID is null and WhiteLabeLID 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 and [AffiliateID] is null and [WhiteLabeLID] is null

[CustomerTypeID] = 1 and [ContentSiteID] is not null and [ResellerID] is null and [AffiliateID] is null and [WhiteLabeLID] is null )

This is a different logic because the OR is not prioritised above the AND.

Any solution or should I move my condition to the table's Update and Insert triggers?

Thanks!

Ofer

You are safe because of the precedence of the operators. A reading from the Books On-Line:

When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Arithmetic (and bitwise) operators are handled before logical operators.

If you swap the Ands and the OR in your expression, you will see that the evaluation order is preserved with parens.

Here is a quick sample with the results shown.

drop table tester

go

create table tester(

one int null,

two int null,

three varchar(10) null

)

go

alter table tester

Add Constraint AndOrAnd Check ( (one = 1 and three is null ) or (two = 0 and three is not null ) )

alter table tester

Add Constraint OrAndOr Check ( (one = 1 or three is null ) and (two = 0 or three is not null ) )

exec sp_helptext AndOrAnd

-->([one] = 1 and [three] is null or [two] = 0 and [three] is not null)

exec sp_helptext OrAndOr

-->(([one] = 1 or [three] is null) and ([two] = 0 or [three] is not null))|||

Thanks for your reply but it doesn't answer this problem.

I need the check constraint to have a codition of ( (x=1 and y is null) or (x=2 and y is not null) )

Replacing the or-s with and-s does a totally different check.

The SQL Server just drops the inner paranthesis, again changing the condition.

Is there a way to check my condition ?

Thanks.

|||

Sorry that my response was not clear. Your expression is being evaluated in the way you desire. It happens because any of the ANDs are being evaluated first. The server is removing the parenthesis precisely because they are redundant to the evaluation.

The example of swapping the conditions was only to show that the server will preserve the parenthesis if they are contrary to normal order of evaluation.

sql

Problem creating a Foreign key Constraint

Hello, I'm having some problems trying to create this foreign key constraint:

ALTER TABLE dbo.t2_demaclie
ADD CONSTRAINT FK03_T2_DEMACLIE FOREIGN KEY (dclPerfilCompania, dclOrdenPedido)
REFERENCES DBO.T2_PEDIDOCLIENTE (cdPerfilCompania, nmOrdenPedido)

Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint 'FK03_T2_DEMACLIE'. The conflict occurred in database 'Comfruta_dllo', table 't2_pedidoCliente'.

I'm sure there's no other constraint with the same name, and there's no othe one with the same columns...

Thanks a lot !!

Sounds like you have rows in t2_demaclie which don't match rows in t2_pedidocliente.

Check this first and see how you go. Something like this should do the trick (should give you zero rows):

select *
from t2_demaclie d
where not exists (select * from t2_pedidocliente p where p.cdPerfilCompania = d.dclPerfilCompania and p.nmOrdenPedido = d.dclOrdenPedido)

Rob|||Very useful ... Thank you so much !|||:) No problem.