From: | Nathaniel <naptrel(at)yahoo(dot)co(dot)uk> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Adding a conditional unique constraint |
Date: | 2007-04-19 09:52:16 |
Message-ID: | 2091E15D-E879-4A2A-B609-3DB9D99D7A2B@yahoo.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Phillip Smith | 2007-04-19 11:51:26 | Re: Adding a conditional unique constraint |
Previous Message | Ashish Karalkar | 2007-04-17 06:46:24 | seeking PITR archive_command advice |