Re: How to create unique constraint on NULL columns

In article <db8s6o$f36$1(at)news(dot)hub(dot)org>, Andrus <eetasoft(at)online(dot)ee> wrote:
% > Then redesign this as a many to many relation. That way someone can
% > have access to one, two, three, four, or all departments.
%
% This means adding separate row for each department into permission table.
% If new department is added, I must determine in some way users which are
% allowed access to all
% departments and add nw rows to permission table automatically.
%
% It seems more reasonable to use NULL department value as "do'nt know, all
% departments allowed"

But wouldn't you want to have an entry in the department table with
NULL for the department ID? I mean, why should NULL act like NULL wrt
foreign keys, but not wrt unique constraints?

--

Patrick TJ McPhee
North York Canada
ptjm(at)interlog(dot)com