CODEWITHSUNDEEP
mysqlconstraintsunique
Vincent10z
Vincent10z

Reputation: 13

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.

Upvotes: 1

Views: 6050

Answers (2)

gview
gview

Reputation: 15361

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.

Upvotes: 1

JustAPup
JustAPup

Reputation: 1780

What it's telling you is that it cannot add this unique constraint. The reason is that in the physician table, there are already entries with same SSN values. You can delete those duplicate records, then run your ALTER statement.

Upvotes: 2

Related Questions