Duplicate Entry for Key Primary on Insert – Fix in 30 Seconds

You're trying to insert a row with a primary key that already exists. This happens a lot when you forget the auto-increment or re-run a script. I'll show you how to fix it in 30 seconds or less.

First, What's Happening?

You ran an INSERT statement and got something like ERROR 1062: Duplicate entry '42' for key 'PRIMARY'. This means the table already has a row with a primary key value of 42. Your INSERT is trying to add another row with the same key. MySQL won't let you – that's what a primary key is for: unique rows.

I've seen this a hundred times. Often it's because you're re-running a script that already inserted data, or you didn't notice the auto-increment counter got out of sync. Let's fix it.

Step 1: The 30-Second Fix – Check Your Data

Open your MySQL client or tool like phpMyAdmin. Run this:

SELECT * FROM your_table_name WHERE primary_key_column = 42;

Replace your_table_name and primary_key_column with your actual table and column names. If you get a row back, that's your duplicate. Decide: Do you want to keep the existing row or replace it?

To keep the existing row: Change your INSERT to use a different primary key value. Just skip that row or modify your data.
To replace it: Delete the old row first: DELETE FROM your_table_name WHERE primary_key_column = 42; Then re-run your INSERT. Or use REPLACE INTO (see below).

But if you're doing a bulk insert or a script, this manual check is too slow. Move to the next step.

Step 2: The 5-Minute Fix – Use INSERT IGNORE or REPLACE

These two statements handle duplicates without throwing an error. They're your friends when you don't care about the conflict.

INSERT IGNORE

This tells MySQL: "Try to insert, but if the primary key already exists, just skip it. No error."

INSERT IGNORE INTO your_table_name (primary_key_column, other_column) VALUES (42, 'some value');

If row 42 exists, MySQL does nothing. No error, no warning. Check the affected rows count – if it's 0, you know it was a duplicate.

REPLACE INTO

This is more aggressive. If the primary key exists, MySQL deletes the old row and inserts the new one. If it doesn't exist, it's a normal insert.

REPLACE INTO your_table_name (primary_key_column, other_column) VALUES (42, 'new value');

Watch out: This changes the row completely. If you only want to update some columns, use ON DUPLICATE KEY UPDATE instead (see advanced).

These two are quick bandaids. They work great for import scripts or when you know duplicates are rare.

Step 3: The 15+ Minute Fix – Fix Auto-Increment or Use ON DUPLICATE KEY UPDATE

If the error keeps happening, the real problem might be a messed-up auto-increment counter. Or you need more control over what happens on conflict.

Fix Auto-Increment

Auto-increment columns track the last value used. But sometimes they get out of sync – like after a manual insert with a high ID. Then MySQL tries to reuse an old value.

Check the current auto-increment value:

SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table_name';

Compare it with the max ID in the table:

SELECT MAX(primary_key_column) FROM your_table_name;

If auto-increment is less than or equal to the max ID, you need to reset it. Run:

ALTER TABLE your_table_name AUTO_INCREMENT = (SELECT MAX(primary_key_column) + 1 FROM your_table_name);

Or if you're using MySQL 8.0, you can set it directly with a number higher than your max ID:

ALTER TABLE your_table_name AUTO_INCREMENT = 1000;

This should stop new inserts from colliding with existing rows.

Use ON DUPLICATE KEY UPDATE

This is the Swiss Army knife for this problem. It lets you control exactly what happens when a duplicate key is found – update specific columns instead of failing or ignoring.

INSERT INTO your_table_name (primary_key_column, column1, column2) VALUES (42, 'value1', 'value2') ON DUPLICATE KEY UPDATE column1 = VALUES(column1), column2 = VALUES(column2);

This updates column1 and column2 to the new values if the row exists. You can also use column1 = column1 + 1 to increment a counter.

I use this all the time for upserts – it's cleaner than REPLACE because it doesn't delete and re-insert.

One gotcha: If your table has multiple unique keys, ON DUPLICATE KEY UPDATE might behave weirdly. But for a single primary key, it's solid.

When to Call It a Day

You should be good now. If you're still stuck, check:

Is your primary key column really auto-increment? Some tables use UUIDs or manual values.
Are you inserting from a file or script that has hardcoded IDs? That's usually the culprit.
Is there a trigger or foreign key constraint causing trouble? Look at the error message closely.

I've fixed this error for dozens of clients. Start with the quick check, then try INSERT IGNORE, and if it's chronic, reset the auto-increment. That covers 99% of cases.