What Happens to Duplicate Records When Adding a UNIQUE Constraint in MySQL?

Determining the Impact of ALTER IGNORE TABLE ADD UNIQUE on Duplicate Records

When attempting to add a UNIQUE constraint on multiple columns to a table, it's inevitable to encounter duplicate records that violate this constraint. In MySQL's earlier versions, the IGNORE clause in the ALTER TABLE statement played a crucial role in handling such duplicates.

MySQL 5.7.4 and Beyond: IGNORE Clause Removed

However, as of MySQL 5.7.4, the IGNORE clause has been deprecated and its use now results in an error. Therefore, the older behavior outlined below is no longer applicable.

Behavior in Older MySQL Versions

For older MySQL versions that support the IGNORE clause, the following behavior occurs when adding a UNIQUE constraint:

• First Row is Preserved: Only the first row among those with duplicate values on the newly unique columns will be retained.
• Other Duplicate Rows are Deleted: The remaining rows with duplicate values will be automatically deleted.
• Truncation of Incorrect Values: Any values that violate other constraints are truncated or modified to fit acceptable limits.

In the example provided, where a UNIQUE constraint is added on (type, owner) to a table containing duplicate records with type = 'Apple' and owner = 'Apple CO', the record with the smallest ID would be the one kept. The other records with the same values would be deleted.

However, it's important to note that this behavior is specific to MySQL versions prior to 5.7.4. In modern MySQL versions, the IGNORE clause is no longer supported, and all conflicting records will cause the ALTER TABLE operation to fail.

The above is the detailed content of What Happens to Duplicate Records When Adding a UNIQUE Constraint in MySQL?. For more information, please follow other related articles on the PHP Chinese website!