Re: How to create unique constraint on NULL columns

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

Some usres may use floow numbers as department codes. They want to use 0 as
ground floor.
Using your suggestion my applicaton does not allow to use some code as
department code.

Should I switch to surrogate primary key in department table? This is major
re-write.