Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Phillip SmithDate: 2007-04-19 11:51:26
Subject: Re: Adding a conditional unique constraint
Previous:From: Ashish KaralkarDate: 2007-04-17 06:46:24
Subject: seeking PITR archive_command advice

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group