Re: How to create unique constraint on NULL columns

Andrus wrote:
>>I was faced with a similar issue.  One suggestion I got from the Internet
>>was to create a shadow column that contains the values used in the Index,
>>with a dummy entry (in my case, the string <NULL>) for those records in
>>which the primary column is NULL.  It works well for my app.
>
>
> Michael,
>
> thank you.
> This is not working in my case. Uniquenes sis not sufficient: I need
> referential integrity with uniqueness. If I add <NULL> department, this must
> be added into department table also and pollutes this table.

Although others have suggested that you're going to need an additional
table, I've not seen anyone explicitly state why this is causing you
problems.

Clearly, NULL should not be used to mean "Any". However, the only reason
you're doing this is because you want a FK to the "department" table.
Nothing wrong with that, except that you're not storing a department-ID.
How do I know? Because you want to store "Any" and that isn't a
department-ID.

So - the obvious solution is a "department_access" table that you can
refer to, and which in turn refers to various departments.

Of course, that's a fair bit of work and you'd like a short-cut. I'm
afraid there isn't one. Half the problems I have with systems I design
are where I've tried to save effort now and end up paying twice over
later on.

--
   Richard Huxton
   Archonet Ltd