Check Constraint Expression error

Question

I'm trying to create a Check Constraint using SQL Server Management Studio 2012's wizard to allow a field to be null only if the tuple is not enabled, but I'm getting an Error validating constraint.

The expression I'm trying to use is this:

TransactionalMerchantId IS NOT NULL
OR
(TransactionalMerchantId IS NULL AND Enabled IS false)

My table is designed as this:

Table Merchant

Can someone help me figure out why this is happening?

score 3 · Accepted Answer · answered Mar 06 '13 at 21:33

3

Try:

TransactionalMerchantId IS NOT NULL
OR
(TransactionalMerchantId IS NULL AND Enabled = 0)

answered Mar 06 '13 at 21:33

Jake H

1,720
1
12
13

Nailed it! Forgot SQL Server didn't automatically render `false` as 0. Thanks! – leobelones Mar 06 '13 at 21:35
This has caught me up a few times. I'm guessing this is so queries with integer types aren't easily incorrectly written (by evaluating the integer value as a boolean, accidentally), similar to many languages comparing equality with == but assignment with =. – Jake H Mar 06 '13 at 21:52

score 0 · Answer 2 · answered Mar 06 '13 at 21:31

0

is is for comparing with null only. Try:

[Enabled] = false

answered Mar 06 '13 at 21:31

Andomar

232,371
49
380
404

partially right, but according to @Jake Heidt's post, sql server doesn't understand false or true, only 0 or 1. But thanks for the insight anyways! – leobelones Mar 06 '13 at 21:36

Check Constraint Expression error

2 Answers2