In-depth Analysis and Solutions for MySQL Composite Primary Key Insertion Anomaly: #1062 Error Without Duplicate Entries

Problem Phenomenon and Background

In MySQL database development, composite primary keys are a common design pattern that uniquely identify each row in a table through a combination of multiple columns. However, developers may encounter a perplexing error during operations: when attempting to insert a record with a new combination of values in the composite primary key columns, the system returns error code #1062, indicating "Duplicate entry," even though queries confirm that no such primary key combination exists in the table.

This article analyzes a typical technical Q&A case. The user employs MySQL 5.1.56 with the MyISAM storage engine and creates a table my_table with a composite primary key on columns number and name. The table initially contains two rows: (1, 'S. Name', 150) and (2, 'Another Name', 284). When trying to insert a new row (2, 'S. Name', 240), the system reports "Duplicate entry '2-S. Name' for key 'PRIMARY'," yet the primary key combination (2, 'S. Name') clearly does not exist in the current data.

Core Problem Analysis

According to the best answer (score 10.0) from the Q&A data, the root cause likely lies in a discrepancy between the actual table structure and the expected one. The user uses a CREATE TABLE IF NOT EXISTS statement to create the table, but this statement only executes if the table does not exist. If the table already exists (possibly due to prior operations), the creation is skipped, and the user might not realize that the current table structure is not the latest version defined in the code.

To verify this, one can run the DESCRIBE my_table; command to inspect the actual table structure. If the output shows that the primary key definition differs from the code (e.g., the primary key includes only a single column or has unintended constraints), the insertion will fail due to a primary key conflict. In the MyISAM engine, such inconsistencies are particularly prone to occur because its table structure management is relatively simple and lacks the transactional guarantees of InnoDB.

Solutions and Steps

Based on the best answer's recommendations, the standard procedure to resolve this issue is as follows:

1. Backup Data: First, backup the data from my_table to a secure location using SELECT * INTO OUTFILE or export tools to prevent data loss.
2. Drop the Old Table: Execute DROP TABLE my_table; to completely remove the existing table. Note that this operation is irreversible, so it must be performed after backup.
3. Rebuild Table Structure: Run the original creation statement to ensure the table is rebuilt as intended. For example: CREATE TABLE my_table (number int(11) NOT NULL, name varchar(50) NOT NULL, money int(11) NOT NULL, PRIMARY KEY (number, name)) ENGINE=MyISAM;. To avoid silent failures that may arise from the IF NOT EXISTS clause, it is advisable to use this clause only when explicitly needed.
4. Restore Data: Reimport the backed-up data into the new table and verify that insertion operations proceed normally.

Additionally, the best answer mentions that explicitly adding a UNIQUE (number, name) constraint can enhance uniqueness assurance, but this is often redundant with a composite primary key, as the primary key inherently implies uniqueness.

Other Potential Factors and Supplements

Referencing other answers (scores 7.8 and 2.6), the following factors might also cause similar "Duplicate entry" errors, although they are not the primary causes in this case:

• NULL Value Handling: If the composite primary key columns contain NULL values, MySQL might treat them as duplicate entries under certain configurations, as NULL behavior in uniqueness constraints is special. For instance, when multiple rows have partial NULL values in composite primary key columns, errors can be triggered. Solutions include ensuring primary key columns are defined as NOT NULL or adjusting data logic.
• Character Set and Collation: With case-insensitive collations (e.g., utf8_general_ci), strings like "Abc" and "abc" may be considered identical, leading to uniqueness conflicts. If case sensitivity is required by the business, switch to case-sensitive collations (e.g., utf8_bin or utf8_general_cs).

Conclusion and Best Practices

This article provides an in-depth analysis of the issue where inserting data into a MySQL table with a composite primary key results in a #1062 error without duplicate entries, using a specific case study. The core cause is table structure inconsistency, and solutions involve checking with the DESCRIBE command and rebuilding the table. Developers should also be aware of potential factors like NULL values and collation rules. In daily development, it is recommended to: regularly verify that table structures match expectations; use the IF NOT EXISTS clause cautiously to avoid silent errors; and clarify collation requirements for string comparisons. These practices help reduce similar errors and improve the reliability of database operations.