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

Question

Trying to create this example table structure in Postgres 9.1:

CREATE TABLE foo (
    name        VARCHAR(256) PRIMARY KEY
);

CREATE TABLE bar (
    pkey        SERIAL PRIMARY KEY,
    foo_fk      VARCHAR(256) NOT NULL REFERENCES foo(name), 
    name        VARCHAR(256) NOT NULL, 
    UNIQUE (foo_fk,name)
);

CREATE TABLE baz(   
    pkey        SERIAL PRIMARY KEY,
    bar_fk      VARCHAR(256) NOT NULL REFERENCES bar(name),
    name        VARCHAR(256)
);

Running the above code produces an error, which does not make sense to me:

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
NOTICE:  CREATE TABLE will create implicit sequence "bar_pkey_seq" for serial column "bar.pkey"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for table "bar"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "bar_foo_fk_name_key" for table "bar"
NOTICE:  CREATE TABLE will create implicit sequence "baz_pkey_seq" for serial column "baz.pkey"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "baz_pkey" for table "baz"
ERROR:  there is no unique constraint matching given keys for referenced table "bar"

********** Error **********

ERROR: there is no unique constraint matching given keys for referenced table "bar"
SQL state: 42830

Can anyone explain why this error arises?

score 319 · Accepted Answer · edited Nov 05 '15 at 13:19

319

It's because the name column on the bar table does not have the UNIQUE constraint.

So imagine you have 2 rows on the bar table that contain the name 'ams' and you insert a row on baz with 'ams' on bar_fk, which row on bar would it be referring since there are two rows matching?

edited Nov 05 '15 at 13:19

Vlastimil Ovčáčík

2,799
27
29

answered Aug 15 '12 at 09:00

Diego

34,802
21
91
134

3

Both of them maybe? isn't that how one-to-many relation work? – shamaseen Feb 05 '23 at 03:27

Matteo Tassinari · Answer 2 · 2017-05-11T06:57:18.707

135

In postgresql all foreign keys must reference a unique key in the parent table, so in your bar table you must have a unique (name) index.

See also http://www.postgresql.org/docs/9.1/static/ddl-constraints.html#DDL-CONSTRAINTS-FK and specifically:

Finally, we should mention that a foreign key must reference columns that either are a primary key or form a unique constraint.

Emphasis mine.

edited May 11 '17 at 06:57

answered Aug 15 '12 at 08:54

Matteo Tassinari

18,121
8
60
81

55

why isn't the declared PK considered as a unique constraint ? it's not like you can have a nonunique PK... – amphibient Sep 07 '16 at 18:14
3

It must be unique on the table it "points to", because if it is not, the database engine will have no way to know which row you are actually referring to. – Matteo Tassinari Sep 07 '16 at 18:15
1

Composite keys? @amphibient – Charming Robot Dec 21 '18 at 22:41
1

I think having a unique key on the referenced column on parent table is not required in postgresql only but also it other RDBMSs too like oracle, sql server etc. – Mufachir Hossain May 12 '19 at 04:32
Maybe, but certainly not all of them, for example MySQL requires just a simple index, which may be non unique – Matteo Tassinari May 12 '19 at 07:09
2

Note that the answer is true also for composite foreign keys, where a composite unique constraint or primary key is required on the parent table. – Ninjakannon Jun 13 '19 at 15:17
@CharmingRobot "it can be said that the PRIMARY KEY of a table is a combination of NOT NULL and UNIQUE constraint." https://www.w3resource.com/PostgreSQL/primary-key-constraint.php – Marc Sloth Eastman Oct 27 '19 at 22:32
@amphibient Because the `UNIQUE` constraint is required on the target column(s). The FK targets `bar.name`, but the PK is on `bar.id`. – Erwin Brandstetter Aug 09 '21 at 02:07

score 16 · Answer 3 · edited May 12 '19 at 04:15

You should have name column as a unique constraint. here is a 3 lines of code to change your issues

First find out the primary key constraints by typing this code
```
\d table_name
```
you are shown like this at bottom "some_constraint" PRIMARY KEY, btree (column)

Drop the constraint:

ALTER TABLE table_name DROP CONSTRAINT some_constraint

Add a new primary key column with existing one:

ALTER TABLE table_name ADD CONSTRAINT some_constraint PRIMARY KEY(COLUMN_NAME1,COLUMN_NAME2);

That's All.

score 11 · Answer 4 · edited Jul 16 '21 at 08:53

when you do UNIQUE as a table level constraint as you have done then what your defining is a bit like a composite primary key see ddl constraints, here is an extract

This specifies that the combination of values in the indicated columns is unique across the whole table, though any one of the columns need not be (and ordinarily isn't) unique.

this means that either field could possibly have a non unique value provided the combination is unique and this does not match your foreign key constraint.

most likely you want the constraint to be at column level. so rather then define them as table level constraints, 'append' UNIQUE to the end of the column definition like name VARCHAR(60) NOT NULL UNIQUE or specify indivdual table level constraints for each field.

Column level constraint in my situation will not work I really should be defining a compound primary key, but I backed away from it because mapping it to JPA its a bit of a pain :) — ams, Aug 15 '12 at 09:15

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

4 Answers4

Linked

Related