Adding a conditional unique constraint
От | Nathaniel |
---|---|
Тема | Adding a conditional unique constraint |
Дата | |
Msg-id | 2091E15D-E879-4A2A-B609-3DB9D99D7A2B@yahoo.co.uk обсуждение исходный текст |
Ответы |
Re: Adding a conditional unique constraint
Re: Adding a conditional unique constraint |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: