Обсуждение: Adding a conditional unique constraint

Hello,

I have a "proposals" table which includes the columns
"draft" (boolean) and "user_id" (integer foreign key into a "users"
table).

The former says whether the record is a draft proposal. The latter
says who created/owns the proposal.

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.

If I use the following:

   ALTER TABLE proposals ADD CONSTRAINT one_draft_each UNIQUE
(user_id, draft)

then this enforces (1) above, but means each user can have only one
submitted proposal.

I can't find anything in the postgres manual, but is is possible to
put conditions on a UNIQUE constraint, perhaps through a CHECK
constraint that something like

   ALTER TABLE proposals ADD CONSTRAINT one_draft_each CHECK draft IS
false OR UNIQUE user_id

?

Thanks for any help,

Nathaniel

ALTER TABLE proposals
ADD CONSTRAINT one_draft_each
CHECK  (draft IS false)
OR    (draft IS true AND user_id NOT IN (SELECT user_id FROM proposals WHERE draft IS true))

It will be a lot slower than a UNIQUE constraint, especially as your table gets bigger...


On Thu, 2007-04-19 at 10:52 +0100, Nathaniel wrote:

ALTER TABLE proposals ADD CONSTRAINT one_draft_each CHECK draft IS 
false OR UNIQUE user_id

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments

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

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
>