I have a database table to store technicians (employees) which is structured like this:

| firstName | lastName | phone | alternatePhone | email | canadaTravel | canDrive | notes |

In this table, only the first two columns are required. Any other values can be null. I would like to prevent against duplicate employees, but I realize this key cannot be made based on name; There may be two John Smiths that we hire.

My initial thought was to expand a key to (first, last, phone) or (first, last, email) but because those are nullable fields, it is not guaranteed to be unique.

Is there a way to design the table to allow for two different people with the same name, but prevent the same person being entered twice?

  • 1
    Probably no unless your "real" people have some unique identifier (SSN, passport id or something) - and real person can have two phone numbers, two emails.. even two passports. This is not a task for the DB layer.
    – jkavalik
    Commented Aug 7, 2015 at 14:05
  • In that case you need to intruduce column which can be unique key - like SSN. Commented Aug 7, 2015 at 14:05
  • 1
    There are lots of ways to re-design the table to prevent duplicates. There is nothing you can do with your current structure to prevent duplicates as is.
    – Dave
    Commented Aug 7, 2015 at 15:13
  • 4
    You should NOT require and store SSN or similar "sensitive" info just because you want something unique - people (HR etc) should enforce that. If you expect duplicating will happen often, do some in-app things to fight common reasons (show existing rows with similar name and other info and "Is the person already present?" or similar - and make a tool which can merge those two in case it actually happens. There are things computers just cannot solve 100%
    – jkavalik
    Commented Aug 7, 2015 at 18:46
  • 3
    @jkavalik said it - SSN et al for the purpose of de-duplication is a bad idea. If you're pulling from another source system us something like an Employee ID and let that system handle duplicate merges. In my DW we run a weekly SSIS package that reviews a person table and pulls likely duplicates based on Name, DOB, etc. and presents them for user review and data clean-up in the source systems we pull from.
    – Dave
    Commented Aug 7, 2015 at 20:17

3 Answers 3


There is no way to allow for two technicians with the same name and still prevent duplicate technician's from being entered without always providing an additional characteristic that will differentiate them to the DBMS as part of a unique constraint. The DBMS does not reflect reality but instead only what you have asserted to it about reality. It cannot determine if two technician's are really the same technician. Instead it can only determine if the data entered is consistent with the constraints declared to it.

Therefore, if you want to be able to enter two people with the same name you must provide additional characteristics (columns) that you require upon entry so that the DBMS can check their values against existing values and differentiate. You are thinking along the right lines - adding phone number or email. But to do so those columns must become required. Short of doing this there is really no other way.

  • You are right, and I have talked this over with the DBA and we are going to work with administration to determine a third, required field. Likely one of the two that exist, as you mentioned. Thanks!
    – AdamMc331
    Commented Aug 18, 2015 at 17:09
  • Glad I was able to help! Commented Aug 18, 2015 at 17:21

Perhaps you will solve this problem with little help from your UI person. Check for the presence of the name in the DB before making a new entry. If similar name is found in the DB, show some kind of warning in the UI that the user should be able to override.

Otherwise, as you perhaps realize, you can either have a unique constraint in the DB or not.


Use the employee's Social Insurance Number (Canadian) or the US equivalent. Do you display this value, but use it to uniquely identify the person in your employee table. Assign a unique EmployeeID (generated primary key) and use this value to identify the employees.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.