CODEWITHSUNDEEP
sql
mat.viguier
mat.viguier

Reputation: 135

Where is my syntax error in this SQL CHECK CONSTRAINT?

I Apologize, but I have exposed my question to google_and_ChatGPT. I Think a need a ... human. Is my question so hard ?

To me, yes.

I have two colums in a Table in a SQL Server database. I have checked that (int)Logicial_Value stays into [0;2] by

Logical_Value BETWEEN 0 AND 2

I need to check if

Int_Value < 5000 WHEN Logical_Value = 0

Int_Value > 9999 WHEN Logical_Value = 2

(Gift : I let you guess what the Logical_Value = 1 should imply)

My Check Constraint Expression is :

CASE
WHEN Logical_Value = 0 THEN Int_Value < 5000
WHEN Logical_Value = 1 THEN Int_Value > 4999 AND Int_Value < 10000
WHEN Logical_Value = 2 THEN Int_Value > 9999
END

The hell, it doesn't work. It just allow any value for Int_Value at each case. Note that SQL Server Management Studio throws an Error Validating Constraint "foo".

My question is now why it let values (it cannot validate the constraint dear captain) but why it can't validate, and thus, what is the correct syntax ?

I'm feeling I have already RTFM, yes.

Upvotes: 0

Views: 113

Answers (1)

The Impaler
The Impaler

Reputation: 48770

You need to rephrase your check constraint with a boolean predicate that doesn't return false for invalid combinations of values. For example:

check (logical_value = 0 and int_value < 5000
    or logical_value = 1 and int_value between 5000 and 9999
    or logical_value = 2 and int_value > 9999)

Upvotes: 1

Related Questions