From: | Richard_D_Levine(at)raytheon(dot)com |
---|---|
To: | Bruno Wolff III <bruno(at)wolff(dot)to> |
Cc: | Andrus <eetasoft(at)online(dot)ee>, pgsql-general(at)postgresql(dot)org, pgsql-general-owner(at)postgresql(dot)org |
Subject: | Re: How to create unique constraint on NULL columns |
Date: | 2005-07-15 20:16:20 |
Message-ID: | OFC81E21F9.6AEDE7BE-ON0525703F.006F22CF-0525703F.006F5C10@ftw.us.ray.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2005-07-15 20:45:51 | Re: How to create unique constraint on NULL columns |
Previous Message | Bruno Wolff III | 2005-07-15 19:49:09 | Re: How to create unique constraint on NULL columns |