In SQL can I use a CASE WHEN statement to set the default of one column if one or more other columns meet a certain condition?

Question

For example, if I want this table to automatically recognise that people that own both cats and dogs are cool. Something like:

CREATE TABLE my_new_table ( name            VARCHAR(13),<p>
                DateOBirth  DATE        NOT NULL,<p>
                has_a_dog   BOOLEAN,<p>
                has_a_cat   BOOLEAN,<p>
                is_really_cool  BOOLEAN,<p>
                CHECK (CASE WHEN    has_a_dog = TRUE<p>
                            AND has_a_cat = TRUE <p>
                            THEN is_really_cool = TRUE));<p>

This isn't a valid syntax, look for conditional constraints and you will find many posts on the subject. — Yosi Dahari, Dec 07 '13 at 11:20

score 1 · Answer 1 · answered Dec 07 '13 at 11:23

1

You can create a computed column if you wanted to. For example:

ALTER TABLE my_new_table ADD is_really_cool AS CASE WHEN has_a_dog = 1 AND has_a_cat = 1 THEN 1 ELSE 0 END

This would show as a new column called is_really_cool and it would evaluate the fields has_a_dog and has_a_cat in the row to determine what it should show.

I've assumed your using MSSQL server

answered Dec 07 '13 at 11:23

JBond

3,062
5
27
31

... Which is unlikely, because SQL Server doesn't have a `BOOLEAN` type as standard (use of bit type notwithstanding). That said, I like this idea a lot anyways – Clockwork-Muse Dec 07 '13 at 11:30
Having been on stack overflow for a while. There's been many times where people have entered the wrong information in there questions. Hence my caveat, it was worth the post just in case :) – JBond Dec 07 '13 at 11:46
Hate to say it, but this answer is cleaner than mine. Voting up. – asantaballa Dec 08 '13 at 13:57

score 0 · Answer 2 · answered Dec 07 '13 at 11:20

0

Not quite, but you could create the table, then make a view over the table which does have that ability and use the view in your applications.

answered Dec 07 '13 at 11:20

asantaballa

3,919
1
21
22

score 0 · Answer 3 · answered Dec 07 '13 at 11:23

You cannot make value of is_Really_cool dependent on other columns in a table during dml, but you can restrict certain combinations. For example (pseudocode):

check (
(has_a_dog = true and has_a_cat = true and is_really_cool = true) or 
(false in (has_a_dog, has_a_cat)
)

Which means you cannot have rows with is_really_cool = true AND not having a dog and a cat.

In SQL can I use a CASE WHEN statement to set the default of one column if one or more other columns meet a certain condition?

3 Answers3