Обсуждение: 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:
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 >