A fundamental property of a unique key is that
it must be unique. Making part of that key Nullable destroys this property.
There are two possible solutions to your problem:
-
One way, the wrong way, would be to use some magic date to represent unknown. This just gets you past
the DBMS “problem” but does not solve the problem in a logical sense.
Expect problems with two “John Smith” entries having unknown dates
of birth. Are these guys one and the same or are they unique individuals?
If you know they are different then you are back to the same old problem –
your Unique Key just isn’t unique. Don’t even think about assigning a whole range of magic dates
to represent “unknown” – this is truly the road to hell. -
A better way is to create an EmployeeId attribute as a surrogate key. This is just an
arbitrary identifier that you assign to individuals that you know are unique. This
identifier is often just an integer value.
Then create an Employee table to relate the EmployeeId (unique, non-nullable
key) to what you believe are the dependant attributers, in this case
Name and Date of Birth (any of which may be nullable). Use the EmployeeId surrogate key everywhere that you
previously used the Name/Date-of-Birth. This adds a new table to your system but
solves the problem of unknown values in a robust manner.