Fix “No Unique Constraint Matching Given Keys” in SQL

The symptom

A CREATE TABLE or ALTER TABLE with a foreign key fails before any row is written. PostgreSQL says there is no unique constraint matching the given keys, SQL Server says there are no primary or candidate keys in the referenced table, and DuckDB says it failed to create the foreign key.

A foreign key promises that each child row points at exactly one parent row. The database can only keep that promise if the parent column can't hold the same value twice, so it must be a PRIMARY KEY or UNIQUE. Here plant_tags.tag_id is a plain column:

The fix: give the parent a key

drop table if exists product_tags;
create or replace table plant_tags (
  tag_id   integer,
  tag_name varchar
);
create table product_tags (
  product_id integer,
  tag_id     integer references plant_tags (tag_id)
);

drop table if exists product_tags;
create or replace table plant_tags (
  tag_id   integer primary key,
  tag_name varchar
);
create table product_tags (
  product_id integer,
  tag_id     integer references plant_tags (tag_id)
);
insert into plant_tags values (1, 'shade'), (2, 'pet safe');
insert into product_tags values (4, 1), (4, 2);
select * from product_tags;

Both versions start by dropping product_tags: DuckDB won't replace plant_tags while a foreign key from an earlier run still points at it.

When the parent table already holds rows

Rather than rebuilding the parent, add the key to it. DuckDB, PostgreSQL, and MySQL accept ALTER TABLE ... ADD PRIMARY KEY:

drop table if exists product_tags;
create or replace table plant_tags (
  tag_id   integer,
  tag_name varchar
);
insert into plant_tags values (1, 'shade'), (2, 'pet safe');
alter table plant_tags add primary key (tag_id);
create table product_tags (
  product_id integer,
  tag_id     integer references plant_tags (tag_id)
);
select 'product_tags created' as result;

Adding the key fails if the column already holds a duplicate. DuckDB says Data contains duplicates on indexed column(s). Find and fix the duplicates with group by tag_id having count(*) > 1 first. A duplicate there means the foreign key couldn't have worked anyway: a child row would point at two parents.

A key on a different column

The key has to be on the column the foreign key names. Pointing at tag_name fails even though tag_id is a primary key, and DuckDB words it differently:

drop table if exists product_tags;
create table product_tags (
  product_id integer,
  tag_name   varchar references plant_tags (tag_name)
);

That is referenced table "plant_tags" does not have a primary key or unique constraint on the columns tag_name. Point at the key, or add UNIQUE to tag_name if names really are unique. The same applies to a composite key: a foreign key must name all of its columns, not the first one.

drop table if exists product_tags;
create or replace table product_keys as
select product_id, product_name
from products;
alter table product_keys add primary key (product_id);
create table product_tags (
  product_id bigint references product_keys (product_id),
  tag_id     integer
);
insert into product_tags values (4, 1);
select * from product_tags;

How other engines word it

Related errors

• Fix “Violates Foreign Key Constraint” in SQL

  A child row points at a parent that is not there, or a parent still has children.

• Fix “Duplicate Key Violates Primary Key Constraint” in SQL

  The key you are inserting already exists. Find the existing row before you write.

• Fix “Catalog Error: Table Does Not Exist” in SQL

  No table by that name is visible to the query. A typo, a missing schema, or a table that's already gone.

Learn more

• SQL error messages explained
• Lesson 8.9 · Rules with constraints
• Reference · Table constraints
• Reference · ALTER TABLE