Re: How to create unique constraint on NULL columns

On Fri, 2005-07-15 at 15:16, Richard_D_Levine(at)raytheon(dot)com wrote:
> 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.

So, does your system currently support >1 departments for those that
would need it? Because if the way you're doing it now doesn't, and you
have to change it to support that at some later date, that will be much
more work than doing it now.