Adding a conditional unique constraint

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

Responses

Browse pgsql-novice by date

  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