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.