0

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.

Improve this question
5
  • 3
    CASE does not work that way. The syntax is CASE WHEN <boolean expression> THEN <value> ELSE <value> END. Notably, boolean expressions in T-SQL are only allowed in specific contexts and nowhere else (there is no true independent boolean type), so you cannot use CASE to specify a condition here at all (unless you artificially compare to 1 or 0 or suchlike). You need to rewrite this using AND an OR, e.g. (Logical_Value = 0 AND Int_Value < 5000) OR (Logical_Value = 1 AND ...). Commented Feb 10, 2023 at 15:47
  • You are not understanding the case expression - it returns a single scalar value, it does not perform control-of-flow or boolean logic. Commented Feb 10, 2023 at 15:49
  • Never (even in SQL dialects that support booleans) use case expression to calculate booleans based on some conditions (other booleans). It is much simpler, logically correct and readable to express this with boolean conditions and operators (and, or, not) Commented Feb 10, 2023 at 15:55
  • @astentx I would think that PostgreSQL behaves correctly with boolean predicates... in all combinations of CHECK and CASE, AND, OR, etc. Commented Feb 10, 2023 at 15:58
  • @TheImpaler No doubts, but what is the purpose to write case instead of and for booleans? If you want A and B to be true at the same time, then A and B is clear and works the same in every DBMS while case when A then B end does not. The only possible case may be to apply some conditions that will evaluate to error otherwise (for ex, when data_type = 'int' then value = 5 when data_type = 'date' then value = date '1970-01-01' end) Commented Feb 10, 2023 at 19:31

1 Answer 1

Reset to default
1

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I always get nervous when AND an OR are mixed without parentheses to specify the precedence, even in cases where it happens to be correct. I've seen too many queries fail on someone accidentally adding a clause where it shouldn't have gone and wondering why they get back all or no rows.
@nbk I think it should work correclly. Is there any missing parenthesis?
@nbk no parens needed here, and looks very clean to me.
@TheImpaler yes it is correct but with parenthesis it would be clearer and more understandable,
A software developer who can't understand the precedence here should get a new job.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.