On Fri, Jul 15, 2005 at 20:08:32 +0300,
Andrus <eetasoft(at)online(dot)ee> wrote:
>
> 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'))
If you are going to do this a partial index is a better way to go.
Something like:
CREATE UNIQUE INDEX user_id_permission_id_null ON permission
WHERE department_id IS NULL;
However either of these let you insert and entry for "ALL" while also
having entries for individual departments.