From: | "Andrus" <eetasoft(at)online(dot)ee> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to create unique constraint on NULL columns |
Date: | 2005-07-18 11:24:07 |
Message-ID: | dbg5jf$2hei$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
<Richard_D_Levine(at)raytheon(dot)com> wrote in message
news:OFC81E21F9(dot)6AEDE7BE-ON0525703F(dot)006F22CF-0525703F(dot)006F5C10(at)ftw(dot)us(dot)ray(dot)com(dot)(dot)(dot)
>
>
> pgsql-general-owner(at)postgresql(dot)org wrote on 07/15/2005 02:49:09 PM:
>
>> On Fri, Jul 15, 2005 at 20:08:32 +0300,
>> Andrus <eetasoft(at)online(dot)ee> wrote:
>> >
>> > So I'll think still continuing to use null as unrestricted department
>> > access.
>> >
>> > Is it reasonable to create unique constraint using
>> >
>> > CREATE UNIQUE INDEX user_id_permission_id_department_id_unique_idx
>> > ON permission (user_id, permission_id, COALESCE(department_id,'ALL'))
>>
>> If you are going to do this a partial index is a better way to go.
>> Something like:
>> CREATE UNIQUE INDEX user_id_permission_id_null ON permission
>> WHERE department_id IS NULL;
>>
>> However either of these let you insert and entry for "ALL" while also
>> having entries for individual departments.
>
> That's a lot of overhead for doing something very simple, like defining a
> department key that means ALL and a row in the foreign table for it to
> point to. Maintaining indices is a nontrivial performance trade-off.
Yes, adding department ALL may be simpler solution.
However, I reference department table from many other tables. In those other
tables, department ALL is NOT ALLOWED.
If I add ALL to department table, I must restrict all other tables of having
ALL department. This is a big work and cannot be done nicely in Postgres.
So I need to allow specify ALL department in privilege table without
changing department table.
Andrus.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2005-07-18 11:35:16 | Re: How to create unique constraint on NULL columns |
Previous Message | Ropel | 2005-07-18 11:22:31 | Re: How to find the number of rows deleted |