Re: Adding a conditional unique constraint

I believe you can have a trigger fired on insertion of your proposals
table, in the fired trigger, you can always check whether the row that
is about to be inserted is a draft proposal, and if it is the you can
check whether the user id in the row about to be inserted already has a
draft proposal in the table. If both conditions are true, have your
trigger restrict the insertion, else let it go to the table.
Regards
Abbas

Michael Glaesemann wrote:
>
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>