Re: How to create unique constraint on NULL columns

>>>"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.
>>>
>>I don't understand why the permission_department table is required ?
>>
> I didn't include this because I thought it would be obvious: You have to
> put a unique constraint on that table so as to eliminate the possibility
> of redundant departmental permission rows, as you thought was your
> original problem.

I'm sorry but I still do not understand.
If I add all departments to former permission table, I can put this unique
constraint to former permission table. In this case there is no NULL
department and constranit will work OK.

>>If user is authorized to all departments, I can add separate row for each
>>department to former permission table. So the permission_department table
>>is not required at all
>>
> Except that when abusing the meaning of NULL you can add duplicate rows
> indicating permission for all departments redundantly, which is what you
> originally misidentified as being the problem for which you sought a means
> to put a unique constraint on NULL values. As the first respondent said,
> the problem IS with the design.
>
>>Unfortunately, this approach causes loss of information: it loses the fact
>>that user is allowed to
>>see all departments data. If new department is added, this department
>>should be made accessible
>>for all users which have marked as "access all departments".
>>
> That information is not lost, but it is a little more work to get it: You
> know how many departments there are. Any user that has a count of
> departments equal to the number of existing departments is an "all
> departments" user. You can thus use aggregation to identify the "all
> departments" users and then add a row for them corresponding to the new
> department.

It is advicable to have two types of users: one user ("super department
user") has access to all existing and all departments to be added in future.
Second type of user can have access to all currently existing departments
but NOT in new departments.

Using non-null departments both of those users have exactly the same records
in permission table.
So those users are not distinguished.

Using NULL as meaning of "don'nt know, allow access to all" distinguishes
both tohse users.

Andrus.