Re: How to create unique constraint on NULL columns

> Although others have suggested that you're going to need an additional
> table, I've not seen anyone explicitly state why this is causing you
> problems.
>
> Clearly, NULL should not be used to mean "Any". However, the only reason
> you're doing this is because you want a FK to the "department" table.
> Nothing wrong with that, except that you're not storing a department-ID.
> How do I know? Because you want to store "Any" and that isn't a
> department-ID.
>
> So - the obvious solution is a "department_access" table that you can
> refer to, and which in turn refers to various departments.
>
> Of course, that's a fair bit of work and you'd like a short-cut. I'm
> afraid there isn't one. Half the problems I have with systems I design are
> where I've tried to save effort now and end up paying twice over later on.

Richard,

Thank you.

I asked this hoping there is a simple and correct solution.
Now I understand that implementing this correctly reguires re-design my user
permission tables.

Acually, there can be any number of duplicate records in user permission
table. This does not
affect my application logic and does not cause incorrect behaviour since
permission table is not joined with other tables. Avoiding duplicates is
required only for joins to be working correctly.

So I'm currently staying in a hack by creating unique index and will
continue
struggling with other problems.

There are number of other tables (about 4 from 100 tables) which have
similar referential problems.
It seems now that implementing referential integrity for those tables
requries also major re-design of database and application logic.

I'm in beginning of porting huge application to Postgres in Windows.
There are number of other problems which seems to be more critical.

So I think I will not try to struggle to implement referential integrity of
those tables in Postgres.
Or will I post referential integrity implementation problem with
document/transactions table ... I cannot resist .. ?

Andrus.