Re: How to create unique constraint on NULL columns

On Fri, 2005-07-15 at 10:51, Andrus wrote:
> >> 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  ?

Then redesign this as a many to many relation.  That way someone can
have access to one, two, three, four, or all departments.