Fix: Duplicate entry for key PRIMARY error in PHP - Quick Solutions

The ‘Duplicate entry for key PRIMARY’ error occurs when trying to insert a record with a primary key value that already exists. This error violates the uniqueness constraint of primary keys.

---

Common Causes and Fixes

1. Inserting Duplicate Primary Key

<?php
// ❌ Error: Duplicate primary key
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (1, 'John')");
$stmt->execute();  // First time OK
$stmt->execute();  // Error! Duplicate entry for key PRIMARY
?>

<?php
// ✅ Fixed: Let auto-increment handle ID
$stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->execute(['John']);
$stmt->execute(['Jane']);  // Auto-increment handles IDs
?>

2. Manual ID Assignment

<?php
// ❌ Error: Manually assigning existing ID
$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (1, 'New User')");
$stmt->execute();  // Error if ID 1 already exists
?>

<?php
// ✅ Fixed: Don't specify ID for auto-increment
$stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->execute(['New User']);
?>

3. Using REPLACE Instead of INSERT

<?php
// ❌ Error: Still duplicate key issue
$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (1, 'Updated')");
$stmt->execute();  // Error if ID 1 exists
?>

<?php
// ✅ Fixed: Use REPLACE to update existing
$stmt = $pdo->prepare("REPLACE INTO users (id, name) VALUES (?, ?)");
$stmt->execute([1, 'Updated']);
?>

4. Using INSERT IGNORE

<?php
// ❌ Error: Still fails on duplicate
$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (1, 'Another')");
$stmt->execute();  // Error!
?>

<?php
// ✅ Fixed: Use INSERT IGNORE to skip duplicates
$stmt = $pdo->prepare("INSERT IGNORE INTO users (id, name) VALUES (?, ?)");
$stmt->execute([1, 'Another']);  // Skips if duplicate
?>

5. Using ON DUPLICATE KEY UPDATE

<?php
// ❌ Error: Duplicate key violation
$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (1, 'New Name')");
$stmt->execute();  // Error if ID 1 exists
?>

<?php
// ✅ Fixed: Update if duplicate
$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name)");
$stmt->execute([1, 'New Name']);  // Updates if ID exists
?>

6. Checking Before Insert

<?php
// ❌ Error: No check for existing record
$id = 1;
$stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE id = ?");
$stmt->execute([$id]);
if ($stmt->fetchColumn() == 0) {
    $insertStmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (?, ?)");
    $insertStmt->execute([$id, 'User']);
} else {
    // Handle duplicate case
}
?>

<?php
// ✅ Fixed: Proper duplicate handling
$id = 1;
$stmt = $pdo->prepare("SELECT id FROM users WHERE id = ?");
$stmt->execute([$id]);
if ($stmt->fetch()) {
    // Update existing
    $updateStmt = $pdo->prepare("UPDATE users SET name = ? WHERE id = ?");
    $updateStmt->execute(['Updated Name', $id]);
} else {
    // Insert new
    $insertStmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (?, ?)");
    $insertStmt->execute([$id, 'New User']);
}
?>

7. Auto-Increment Not Working

<?php
// ❌ Error: Manually setting ID when auto-increment should be used
$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (NULL, 'User')");  // May cause issues
$stmt->execute();
?>

<?php
// ✅ Fixed: Don't specify ID for auto-increment
$stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->execute(['User']);  // Auto-increment handles ID
?>

8. Transaction with Duplicate Check

<?php
// ❌ Error: No duplicate prevention in transaction
$pdo->beginTransaction();
$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (?, ?)");
$stmt->execute([1, 'User']);
$pdo->commit();
?>

<?php
// ✅ Fixed: Check for duplicates in transaction
$pdo->beginTransaction();
$stmt = $pdo->prepare("SELECT id FROM users WHERE id = ?");
$stmt->execute([1]);
if (!$stmt->fetch()) {
    $insertStmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (?, ?)");
    $insertStmt->execute([1, 'User']);
    $pdo->commit();
} else {
    $pdo->rollback();
    // Handle duplicate case
}
?>

9. Using UUID Instead of Integer ID

<?php
// ❌ Error: Still duplicate integer ID
$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (?, ?)");
$stmt->execute([1, 'User']);  // Error if ID 1 exists
?>

<?php
// ✅ Fixed: Use UUID for uniqueness
function generateUUID() {
    return sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
        mt_rand(0, 0xffff), mt_rand(0, 0xffff),
        mt_rand(0, 0xffff),
        mt_rand(0, 0x0fff) | 0x4000,
        mt_rand(0, 0x3fff) | 0x8000,
        mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff)
    );
}

$stmt = $pdo->prepare("INSERT INTO users (uuid, name) VALUES (?, ?)");
$stmt->execute([generateUUID(), 'User']);
?>

10. Bulk Insert with Duplicate Handling

<?php
// ❌ Error: Bulk insert with duplicates
$users = [[1, 'John'], [2, 'Jane'], [1, 'Duplicate']];  // ID 1 duplicated
$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (?, ?)");
foreach ($users as $user) {
    $stmt->execute($user);  // Error on duplicate
}
?>

<?php
// ✅ Fixed: Handle duplicates in bulk insert
$users = [[1, 'John'], [2, 'Jane'], [1, 'Updated']];  // ID 1 duplicated
$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name)");
foreach ($users as $user) {
    $stmt->execute($user);  // Updates duplicate instead of error
}
?>

---

Quick Debugging Steps

1. Check table structure for primary key column
2. Verify the ID you’re inserting doesn’t exist
3. Check auto-increment settings if using auto-increment
4. Review your data for duplicate primary key values
5. Use SELECT to verify if record exists before INSERT

---

Prevention Tips

• Use auto-increment for primary keys when possible
• Don’t manually assign primary key values unless necessary
• Use ON DUPLICATE KEY UPDATE for upsert operations
• Use INSERT IGNORE to skip duplicates
• Use REPLACE INTO to update existing records
• Check for existing records before insertion
• Consider using UUIDs for distributed systems
• Handle duplicates gracefully in your application logic

Remember: Primary keys must be unique. Either let auto-increment handle IDs or implement proper duplicate handling.