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

Re: Adding a conditional unique constraint

From: "Abbas" <abbas(dot)butt(at)enterprisedb(dot)com>
To: "Michael Glaesemann" <grzm(at)seespotcode(dot)net>
Cc: "Nathaniel" <naptrel(at)yahoo(dot)co(dot)uk>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: Adding a conditional unique constraint
Date: 2007-04-20 17:19:49
Message-ID: 4628F635.9060707@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>


In response to

pgsql-novice by date

Next:From: Andrew JarchoDate: 2007-04-20 17:47:54
Subject: can i call stored function using ecpg?
Previous:From: Tasneem MemonDate: 2007-04-20 13:20:42
Subject: Help needed regarding the PGSQL Source code Download.

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