Inserting or updating a row that would create a duplicate value in a UNIQUE or PRIMARY KEY column.
This error is thrown when an `INSERT` or `UPDATE` would result in two rows having the same value in a column (or combination of columns) that has a `PRIMARY KEY` or `UNIQUE` constraint. The database rejects the operation to maintain data integrity.
Causes include: inserting a record with an ID that already exists, importing data that contains duplicates, running an insert operation multiple times without checking, a race condition in concurrent inserts, or using a sequence that restarted from 1.
1CREATE TABLE products (2 id INT PRIMARY KEY,3 name VARCHAR(100) NOT NULL,4 sku VARCHAR(50) UNIQUE5);6 7-- First insert succeeds8INSERT INTO products (id, name, sku) VALUES (1, 'Widget A', 'WGT-001');9 10-- Second insert FAILS — id=1 and sku='WGT-001' both already exist11INSERT INTO products (id, name, sku) VALUES (1, 'Widget B', 'WGT-001');12-- ERROR: duplicate key value violates unique constraint "products_pkey"1-- Fix 1: Use INSERT ... ON CONFLICT (PostgreSQL/SQLite)2INSERT INTO products (id, name, sku)3VALUES (1, 'Widget B', 'WGT-002') -- Use a different, unique SKU4ON CONFLICT (id)5DO UPDATE SET name = EXCLUDED.name, sku = EXCLUDED.sku;6 7-- Fix 2: Use INSERT IGNORE (MySQL) — silently skips on conflict8INSERT IGNORE INTO products (id, name, sku)9VALUES (1, 'Widget B', 'WGT-002');10 11-- Fix 3: Check existence first12INSERT INTO products (id, name, sku)13SELECT 2, 'Widget B', 'WGT-002'14WHERE NOT EXISTS (15 SELECT 1 FROM products WHERE id = 2 OR sku = 'WGT-002'16);17 18-- Fix 4: Use auto-increment IDs to avoid manual ID conflicts19CREATE TABLE products (20 id SERIAL PRIMARY KEY, -- PostgreSQL (auto-increment)21 name VARCHAR(100) NOT NULL,22 sku VARCHAR(50) UNIQUE23);24INSERT INTO products (name, sku) VALUES ('Widget C', 'WGT-003'); -- ID auto-assignedSimulate standard system builds to trigger compiler trace records and track memory crashes locally.
The `products` table has `id` as a `PRIMARY KEY` (implicitly unique) and `sku` as `UNIQUE`. The second `INSERT` tries to insert `id=1` and `sku='WGT-001'`, both of which already exist. The database rejects this insert to prevent data integrity violations.