Hello,
I have a "proposals" table which includes the columns
"draft" (boolean) and "user_id" (integer foreign key into a "users"
table).
The former says whether the record is a draft proposal. The latter
says who created/owns the proposal.
Here's my problem. I want to enforce the following through the use of
constraints:
1. Each user can have only one draft proposal.
2. Each user can have any number of non-draft (submitted) proposals.
If I use the following:
ALTER TABLE proposals ADD CONSTRAINT one_draft_each UNIQUE
(user_id, draft)
then this enforces (1) above, but means each user can have only one
submitted proposal.
I can't find anything in the postgres manual, but is is possible to
put conditions on a UNIQUE constraint, perhaps through a CHECK
constraint that something like
ALTER TABLE proposals ADD CONSTRAINT one_draft_each CHECK draft IS
false OR UNIQUE user_id
?
Thanks for any help,
Nathaniel