Re: How to create unique constraint on NULL columns

On 7/18/05, Andrus <eetasoft(at)online(dot)ee> wrote:
> > 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.

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. :)

Regards,
Dawid