Re: How to create unique constraint on NULL columns

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 ?
>
>
>

"if department _id is NULL, user has access to all departments data."

This is your problem. You've assigned meaning to the "value" NULL.

CREATE TABLE permission (
  id serial,
  user_id CHAR(10) NOT NULL REFERENCES user,
  permission_id CHAR(10) NOT NULL  REFERENCES privilege,
  UNIQUE (user_id, permission_id));


CREATE TABLE permission_department (
  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));

Any person who is authorized to access documents of a department MUST
have a corresponding row in permission_department: If they are
authorized to view documents of all departments, then they must have a
row corresponding to every department.