On 7/18/05, Andrus <eetasoft@online.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