What happens to duplicate records when using `ALTER IGNORE TABLE` with a unique constraint in MySQL?

MySQL: ALTER IGNORE TABLE with a Unique Constraint

In MySQL, you may encounter a scenario where you need to add a unique constraint on an existing table. The ALTER TABLE statement offers the IGNORE option that can potentially affect the retention of duplicate records.

Consider the following example:

ALTER IGNORE TABLE `my_table` ADD UNIQUE (`type`, `owner`);

Assume that we have a table my_table with the following schema:

ID (AUTO_INCREMENT PRIMARY KEY)
type
owner
description

Let's say we have multiple records with the same type and owner values, such as:

| ID | type | owner |
|---|---|---|
| 1 | Apple | Apple CO |
| 2 | Apple | Apple CO |

The question arises: which record will remain after adding the unique constraint using ALTER IGNORE TABLE?

In MySQL versions prior to 5.7.4, the IGNORE clause would retain the first record that satisfies the unique constraint, while deleting the rest.

| ID | type | owner |
|---|---|---|
| 1 | Apple | Apple CO |

However, it is important to note that the IGNORE clause has since been removed from MySQL 5.7.4 onwards and its use now results in an error.

Therefore, in newer versions of MySQL, you should avoid using ALTER IGNORE TABLE with unique constraints. Instead, consider using the following alternative:

ALTER TABLE `my_table` ADD UNIQUE (`type`, `owner`) USING BTREE;

In this case, if there are duplicate records that violate the unique constraint, an error will be thrown and the operation will fail. This approach ensures data integrity and prevents any unexpected truncation or deletion of records.

The above is the detailed content of What happens to duplicate records when using `ALTER IGNORE TABLE` with a unique constraint in MySQL?. For more information, please follow other related articles on the PHP Chinese website!