Compact check constraint for nullable columns

[rechkemmer]

The check constraint could be expressed in a more compact/maintainable/extendable form

[parsonsmatt]

Unfortunately, this isn't entirely correct. The original constraint requires that only the correct properties are present, while your constraint ensures that at least the correct properties are present.

[rechkemmer]

A non-portable, but even more compact way would be something like

create table animals
    ( animal_id serial primary key
    , animal_type animal_type not null
    , dog_name text 
        check ((animal_type = 'dog') = (dog_name is not null))
    , dog_age integer
        check ((animal_type = 'dog') = (dog_age is not null))
    , bird_song text
        check ((animal_type = 'bird') = (bird_song is not null))
    );