create table xyz
(
field_foo char(1) check (field_foo in 'y', 'n'),
foo_detail varchar(255),
check (
case
when field_foo='y' and foo_detail is null
then false
else true
end
)
);
A simpler check would be :
CHECK(
(field_foo = 'y' AND foo_detail IS NOT NULL)
OR ( (field_foo = 'n' OR field_foo IS NULL) AND foo_detail IS NULL)
)Which means " field_foo can be y, n, or NULL, and foo_detail should be null except if field_foo is 'y' "
Also, IMHO, the Y/N/unknown case should have three values, NULL meaning 'the user has not answered this question'. Because if you insert a blank row in the table and fill it afterwards, you'll know if it was 'answered unknown' or 'unanswered'.
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
