Re: How to create unique constraint on NULL columns

On Fri, 2005-07-15 at 10:26, Andrus wrote:
> >> How to create constraint so that NULL values are treated equal and second
> >> insert is rejected ?
> >
> > Rethink your data design --- this behavior is required by the SQL
> > standard.
>
> I have a table of users permissions by departments
>
> CREATE TABLE permission (
> id serial,
> user_id CHAR(10) NOT NULL REFERENCES user,
> permission_id CHAR(10) NOT NULL REFERENCES privilege,
> department_id CHAR(10) REFERENCES department ,
> UNIQUE ( user_id, permission_id, department_id ) )
>
> permission_id is a permission name: Invoice, Waybill etc.
>
> department _id is a code of department whose documents user is authorized to
> access.
>
> if department _id is NULL, user has access to all departments data.
>
> By this design it is meaningless to have two records with same user_id and
> permission_id both having department_id NULL
>
> So I want that Postgres does not allow to insert them.
>
> How I should rethink this data design to be implemented in CREATE TABLE
> statement ?

You should use a value like 'ALL' to denote that they have access to all
departments. Null, in this case, would mean you don't know which
departments they have access to, and this is not really the case.