What is causing ERROR: there is no unique constraint matching given keys for referenced table?

Question

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

Answer 1

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.

If you wish to learn SQL then visit this SQL Training.