What is causing ERROR: there is no unique constraint matching given keys for referenced table?
Below example table structure gives an ERROR: there is no unique constraint matching given keys for the referenced table, and having stared at it for while now I can't figure out why this error arises in this situation.
BEGIN;
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)
);
COMMIT;
Running the above code gives the following error, which does not make sense to me, can anyone explain why this error arises. I am using Postgres 9.1
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
Reason of geeting this error: If your query references a column that does not have the UNIQUE constraint, you'll get the “There is no unique constraint matching given keys for referenced table” error. In PostgreSQL, it's vitally important that a foreign key references columns that either are a primary key or form a unique constraint.
As the name column on the bar table does not have the UNIQUE constraint. Let's assume you have 2 rows on the bar table that contain the name 'ams' and you are trying to insert a row on baz with 'ams' on bar_fk, now which row on the bar would it be referring to since there are two rows matching can be taken care.