I have a "proposals" table which includes the columns
"draft" (boolean) and "user_id" (integer foreign key into a "users"
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
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
then this enforces (1) above, but means each user can have only one
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,
pgsql-novice by date
|Next:||From: Phillip Smith||Date: 2007-04-19 11:51:26|
|Subject: Re: Adding a conditional unique constraint|
|Previous:||From: Ashish Karalkar||Date: 2007-04-17 06:46:24|
|Subject: seeking PITR archive_command advice|