No Unique Constraint on Conflict Column

-- Table without unique constraint

CREATE TABLE users (

user_id INT,

username VARCHAR(50),

email VARCHAR(100)

);

-- UPSERT fails: no unique constraint

INSERT INTO users (user_id, username, email)

VALUES (1, 'john_doe', 'john@example.com')

ON CONFLICT (user_id) DO UPDATE SET email = EXCLUDED.email;

-- Error: there is no unique or exclusion constraint matching the ON CONFLICT specification

-- Add unique constraint or primary key

CREATE TABLE users (

user_id INT PRIMARY KEY, -- Primary key

username VARCHAR(50),

email VARCHAR(100) UNIQUE -- Unique constraint

);

-- Or add unique index

CREATE UNIQUE INDEX idx_users_user_id ON users(user_id);

-- Now UPSERT works

INSERT INTO users (user_id, username, email)

VALUES (1, 'john_doe', 'john@example.com')

ON CONFLICT (user_id) DO UPDATE SET email = EXCLUDED.email;

UPSERT requires UNIQUE constraint or PRIMARY KEY on conflict column to detect duplicates. Without constraint, database cannot determine if row exists. PostgreSQL throws error immediately. MySQL silently checks all UNIQUE indexes (can match wrong column). Solution: Add PRIMARY KEY or UNIQUE constraint on conflict column. Use CREATE UNIQUE INDEX if cannot modify table structure. Verify constraint exists before UPSERT: SELECT * FROM pg_indexes WHERE tablename = users; Real-world: Common mistake in development when testing UPSERT on table without proper constraints.

Always ensure conflict column has UNIQUE constraint or PRIMARY KEY. Use CREATE UNIQUE INDEX if cannot modify table. Verify with pg_indexes or SHOW INDEXES.

Critical

PostgreSQL: "there is no unique or exclusion constraint matching the ON CONFLICT specification". MySQL: Works but checks all UNIQUE indexes (can cause unexpected behavior).

graph TB

subgraph "Wrong: No Unique Constraint"

W1["CREATE TABLE users<br/>user_id INT (no constraint)"]

W2["INSERT...ON CONFLICT (user_id)"]

W3["Error: No unique constraint"]

W4["UPSERT fails"]

end

subgraph "Correct: With Unique Constraint"

C1["CREATE TABLE users<br/>user_id INT PRIMARY KEY"]

C2["INSERT...ON CONFLICT (user_id)"]

C3["Conflict detected via index"]

C4["UPSERT succeeds"]