Search code examples
mysqlconstraintsunique

Duplicate entry error when trying to add unique constraint to table.column in MySQL

Like the title says I'm getting the following error when inserting this query into the MySQL Command Line Client

Query

ALTER TABLE physician ADD UNIQUE (ssn);

Error code

ERROR 1062 (23000): Duplicate entry '' for key 'physician.ssn'

I don't know why I'm pulling a duplicate entry error as this shouldn't be a duplicate entry. Any help is appreciated.

Solution

  • A unique constraint will only allow unique values for that column. Since you already have many rows with NULL, MySQL will not allow you to add the constraint.

    You have 2 options:

    1. Update the table with placeholder values for SSN you would be able to identify later. For example, one thing you could do would be to run an update that sets the ssn column = to the table pk/id.
    2. Use a unique index instead. MySQL unique indexes ignore null, but will enforce uniqueness when values are added. It also has the benefit of providing indexed search if you plan to have the system search by ssn.

    The syntax would be:

    CREATE UNIQUE INDEX physician_ssn_idx ON physician(ssn);

    physician_ssn_idx will be the name of the newly created index, which can be anything you want it to be. If you have been creating index/key using a naming scheme you might want to adopt that. Ultimately, the names of indexes don't effect the functionality at all, and you don't need to know them in order to make use of them.