Re: How to create unique constraint on NULL columns

On 7/18/05, Andrus <eetasoft(at)online(dot)ee> wrote:
> >> 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.
> >
> > Not true. :) You simply need to add CHECK (departament_id <> 0) (assuming
> > 0 is the ID of ALL departaments. You can even CREATE DOMAIN with this
> > check "built in" to save you some typing. :)
> >
> > If, for some reason, you want to be sure that 'ALL deparaments' is not
> > visible, you can create a view which will SELECT WHERE departament <> 0;
> >
> > Basically -- I think you should get some pre-declared values, like
> > departament_id
> > of 0 and simply restrict it where it is not allowed. It's better than
> > forcing NULL
> > to become a value. :)
>
> Dawid,
>
> I have meaningful primary key in department table (department code used
> inside enterptise), not a surrogate number (I use meaningful primary keys
> whenever possible).

OK, so then just define the UNIQUE INDEX to be exactly what you need,
for example:

CREATE UNIQUE INDEX my_special_unique ON permission (
user_id,
permisson_id,
(department_id IS NULL),
(CASE when department_id IS NULL THEN 0 ELSE department_id END)
);

This should work for any department_id type. With one little drawback:
person can have permission to ALL departaments (NULL) _and_ also
an explicit permission for any of already existing ones.

HTH, HAND