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

Re: Adding a conditional unique constraint

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Nathaniel <naptrel(at)yahoo(dot)co(dot)uk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Adding a conditional unique constraint
Date: 2007-04-19 12:30:44
Message-ID: 2493FA77-266B-4D42-A092-5D8842089BEB@seespotcode.net (view raw or flat)
Thread:
Lists: pgsql-novice
On Apr 19, 2007, at 4:52 , Nathaniel wrote:

> 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.


I believe you can use a partial index to handle this.

CREATE TABLE proposals (
     proposal text primary key
     , user_id integer not null
     , draft boolean not null
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
"proposals_pkey" for table "proposals"
CREATE TABLE

-- Here's the partial unique index:

CREATE UNIQUE INDEX one_draft_proposal_per_user ON proposals  
(user_id) WHERE draft;
CREATE INDEX

COPY proposals (proposal, user_id, draft) FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
a    1       true
b       1       false
c       2       true
d       2       false
\.
SELECT * FROM proposals;
proposal | user_id | draft
----------+---------+-------
a        |       1 | t
b        |       1 | f
c        |       2 | t
d        |       2 | f
(4 rows)

INSERT INTO proposals (proposal, user_id, draft) VALUES ('e', 1, false);
INSERT 0 1
SELECT * FROM proposals;
proposal | user_id | draft
----------+---------+-------
a        |       1 | t
b        |       1 | f
c        |       2 | t
d        |       2 | f
e        |       1 | f
(5 rows)

INSERT INTO proposals (proposal, user_id, draft) VALUES ('f', 1, true);
ERROR:  duplicate key violates unique constraint  
"one_draft_proposal_per_user"
UPDATE proposals SET DRAFT = true WHERE proposal = 'e';
ERROR:  duplicate key violates unique constraint  
"one_draft_proposal_per_user"
SELECT * FROM proposals;
proposal | user_id | draft
----------+---------+-------
a        |       1 | t
b        |       1 | f
c        |       2 | t
d        |       2 | f
e        |       1 | f
(5 rows)

The documentation has more information here:
http://www.postgresql.org/docs/8.2/interactive/indexes-partial.html

Hope this helps!

Michael Glaesemann
grzm seespotcode net



In response to

Responses

pgsql-novice by date

Next:From: Gary WarnerDate: 2007-04-19 18:15:16
Subject: Visual C++ template?
Previous:From: Phillip SmithDate: 2007-04-19 11:51:26
Subject: Re: Adding a conditional unique constraint

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