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