Re: How to create unique constraint on NULL columns

Andrus wrote:

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

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