This question requires some hypothetical background. Let&#39;s consider an `employee` table that has columns `name`, `date_of_birth`, `title`, `salary`, using MySQL as the RDBMS. Since if any given person has the same name and birth date as another person, they are, by definition, the same person (barring amazing coincidences where we have two people named Abraham Lincoln born on February 12, 1809), we&#39;ll put a unique key on `name` and `date_of_birth` that means &quot;don&#39;t store the same person twice.&quot; Now consider this data:

    id name        date_of_birth title          salary
     1 John Smith  1960-10-02    President      500,000
     2 Jane Doe    1982-05-05    Accountant      80,000
     3 Jim Johnson NULL          Office Manager  40,000
     4 Tim Smith   1899-04-11    Janitor         95,000

If I now try to run the following statement, it should and will fail:

    INSERT INTO employee (name, date_of_birth, title, salary)
    VALUES (&#39;Tim Smith&#39;, &#39;1899-04-11&#39;, &#39;Janitor&#39;, &#39;95,000&#39;)

If I try this one, it will succeed:

    INSERT INTO employee (name, title, salary)
    VALUES (&#39;Jim Johnson&#39;, &#39;Office Manager&#39;, &#39;40,000&#39;)

And now my data will look like this:

    id name        date_of_birth title          salary
     1 John Smith  1960-10-02    President      500,000
     2 Jane Doe    1982-05-05    Accountant      80,000
     3 Jim Johnson NULL          Office Manager  40,000
     4 Tim Smith   1899-04-11    Janitor         95,000
     5 Jim Johnson NULL          Office Manager  40,000

This is not what I want but I can&#39;t say I entirely disagree with what happened. If we talk in terms of mathematical sets,

    {&#39;Tim Smith&#39;, &#39;1899-04-11&#39;} = {&#39;Tim Smith&#39;, &#39;1899-04-11&#39;} &lt;-- TRUE
    {&#39;Tim Smith&#39;, &#39;1899-04-11&#39;} = {&#39;Jane Doe&#39;, &#39;1982-05-05&#39;} &lt;-- FALSE
    {&#39;Tim Smith&#39;, &#39;1899-04-11&#39;} = {&#39;Jim Johnson&#39;, NULL} &lt;-- UNKNOWN
    {&#39;Jim Johnson&#39;, NULL} = {&#39;Jim Johnson&#39;, NULL} &lt;-- UNKNOWN

My guess is that MySQL says, &quot;Since I don&#39;t *know* that Jim Johnson with a `NULL` birth date isn&#39;t already in this table, I&#39;ll add him.&quot;

My question is: **How can I prevent duplicates even though `date_of_birth` is not always known?** The best I&#39;ve come up with so far is to move `date_of_birth` to a different table. The problem with that, however, is that I might end up with, say, two cashiers with the same name, title and salary, different birth dates and no way to store them both without having duplicates.

All Relational Model Solutions on Gang of Coders

Total of 1 Relational Model Solutions

Unique key with NULLs