No unique or exclusion constraint matching the ON CONFLICT

sql

unique-constraints

database-design

conflict-resolution

byAlex Kataev·Dec 12, 2024

When you see "No unique or exclusion constraint" error, adding a unique constraint on the conflicted columns before using the ON CONFLICT is the solution. You can do it this way:

-- Single column unique constraint
ALTER TABLE your_table ADD UNIQUE (column); -- Alright, unique enough!

-- Multi-column unique constraint
ALTER TABLE your_table ADD UNIQUE (column1, column2); -- Twins aren’t unique now, are they?

With the constraint in place, ON CONFLICT behaves as expected:

-- Using ON CONFLICT in an INSERT, it's like chess - gotta think a step ahead!
INSERT INTO your_table (column1, column2) VALUES (value1, value2)
ON CONFLICT (column1, column2) DO UPDATE SET column2 = EXCLUDED.column2;

Ensure a non-partial unique index for successful inference and verify that your primary key constraint or unique index exists and covers all columns in the ON CONFLICT clause.

Making Sense of the ON CONFLICT clause

Use case scenarios and solutions

The need for ON CONFLICT could arise from diverse scenarios. Let’s check out a few and see how it does its juju:

Scenario 1: Multi-tenant databases
In databases with multiple tenants, create partial unique indexes using an index_predicate. Now remember, not every tenant likes the same curtain pattern:

CREATE UNIQUE INDEX idx_unique_col_per_account ON your_table (column) WHERE (account_id = <your_account_id>);

Scenario 2: Handling Nullable Values
You probably heard about NULLs being the joker in the pack. Remember, in the world of unique constraints, they are all different individuals:

CREATE UNIQUE INDEX idx_unique_nullable ON your_table (column1, column2) WHERE (column2 IS NOT NULL);

Scenario 3: Type-Specific Unique Constraints
Ever tried to put a square peg in a round hole? That’s how the database feels when your data types don't match your constraints:

ALTER TABLE your_table ADD UNIQUE (cast(column as uuid));

Debugging techniques

While debugging, remember to test with and without the index_predicate. Syntax errors are like pebbles in your shoe, so get ‘em out. Check for duplicates and don't let unnecessary characters mess with your definitions.

Best practices to follow

For a blissfully ‘errorless’ life, use PRIMARY KEY or UNIQUE CONSTRAINT generally. When dealing with NULL values, consider excluding them from constraints or use the IS NOT DISTINCT FROM operator for a change.

How EXCLUDED saves the day

The EXCLUDED keyword is the superhero of conflict resolution. It represents the row that would have been inserted if there wasn’t a pesky overlap. The EXCLUDED keyword is used within the DO UPDATE action to ensure your intended changes are made.

Partial Indexes and conditional uniqueness

When the road to uniqueness is littered with conditions, partial indexes are your GPS:

CREATE UNIQUE INDEX unique_idx_on_condition ON your_table (column1, column2) WHERE (condition);

By defining conditions, you ensure the ON CONFLICT action is triggered as and when needed.

explain-codes / Sql / No unique or exclusion constraint matching the ON CONFLICT

Linked

Create unique constraint with null columns



How to change a PG column to NULLABLE TRUE?



Can there be constraints with the same name in a DB?



How to create a unique index on a NULL column?



Error: there is no unique constraint matching given keys for referenced table "bar"



Insert data in 3 tables at a time using Postgres



What is this operator <=> in MySQL?

