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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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