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.