Re: How to create unique constraint on NULL columns

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

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

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

Using department ALL breaks the department_id reference to department table.
Inserting department_id ALL is rejected since there is no such department.

So I can use department ALL  ?

Andrus.