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>

Answer 1

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.

Answer 2

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.

Answer 3

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