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

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

So I'll think still continuing to use null as unrestricted department
access.

Is it reasonable to create unique constraint using

CREATE UNIQUE INDEX user_id_permission_id_department_id_unique_idx
ON permission (user_id, permission_id, COALESCE(department_id,'ALL'))

Andrus.