MySQL Cookbook

Dealing with Duplicates at Record-Creation Time

Problem

You’ve created a table with a unique index to prevent duplicate values in the indexed column or columns. But this results in an error if you attempt to insert a duplicate record, and you want to avoid having to deal with such errors.

Solution

One approach is to just ignore the error. Another is to use either an INSERT IGNORE or REPLACE statement, each of which modifies MySQL’s duplicate-handling behavior. For bulk-loading operations, LOAD DATA has modifiers that allow you to specify how to handle duplicates.

Discussion

By default, MySQL generates an error when you insert a record that duplicates an existing unique key. For example, you’ll see the following result if the person table contains a unique index on the last_name and first_name columns:

mysql> INSERT INTO person (last_name, first_name)
    -> VALUES('X1','Y1');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO person (last_name, first_name)
    -> VALUES('X1','Y1');
ERROR 1062 at line 1: Duplicate entry 'X1-Y1' for key 1

If you’re issuing the statements from the mysql program interactively, you can simply say, “Okay, that didn’t work,” ignore the error, and continue. But if you write a program to insert the records, an error may terminate the program. One way to avoid this is to modify the program’s error-handling behavior to trap the error and then ignore it. See Recipe 2.3 for information about error-handling techniques.

If you want to prevent the error from occurring ...