Cause of error
The error message is reported: "Duplicate entry '1234' for key 'USER_INFO.PRIMARY'". When trying to insert a data, the primary key value ID=1234 is already present in the table USER_INFO. Because the Primary Key is a unique identifier in the table, each record must have a unique primary key value, so an error is reported
example:
| ID (primary key) | USER_NAME | ADDRESS |
|---|---|---|
| 1234 | Xiaohong | Wuhan |
| 5678 | noob | Changsha |
The expected error is reported after inserting SQL as follows
INSERT INTO USER_INFO (ID, USER_NAME, ADDRESS) VALUES (1234, 'Xiaohong', 'Wuhan');
Solution
1. INSERT IGNORE: Ignore the repeated insertion operation, the insertion is invalid
mechanism: No operation is performed when encountering primary key conflicts
Sample Expectations：
INSERT IGNORE USER_INFO (ID, USER_NAME, ADDRESS) VALUES (1234, 'Xiaohong', 'Wuhan');
| ID (primary key) | USER_NAME | ADDRESS |
|---|---|---|
| 1234 | Xiaohong | Wuhan |
| 5678 | noob | Changsha |
INTO: Can replace existing records when primary key conflicts
mechanism: First try to insert data. If a primary key conflict (or unique index conflict) is found, delete the conflicting existing rows and then insert a new row
Sample Expectations：
REPLACE INTO USER_INFO (ID, USER_NAME, ADDRESS) VALUES (1234, 'Little Green', 'Beijing');
| ID (primary key) | USER_NAME | ADDRESS |
|---|---|---|
| 1234 | Little green | Beijing |
| 5678 | noob | Changsha |
3. ON DUPLICATE KEY UPDATE: You can update existing records when primary key conflicts
mechanism: First try to insert data. If a primary key conflict (or unique index conflict) is found, then perform the update operation instead of deleting and reinserting (suppose scenario: import data in batches. If the same primary key information already exists in the table, the information in the table shall prevail, and then update an identifier in the table, etc.)
INSERT INTO USER_INFO (ID, USER_NAME, ADDRESS) VALUES (1234, 'Xiaohong', 'Wuhan')
ON DUPLICATE KEY UPDATE USER_NAME = 'Little Green'
Sample Expectations：
| ID (primary key) | USER_NAME | ADDRESS |
|---|---|---|
| 1234 | Little green | Wuhan |
| 5678 | noob | Changsha |
4. Avoid repeated insertion and adjustment of table structure
This is the article about the solution to the SQL Duplicate entry for key ‘PRIMARY’ primary key repeated error response. For more related SQL primary key repeated error response, please search for my previous article or continue browsing the related articles below. I hope everyone will support me in the future!