Re: Adding a conditional unique constraint

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