[sql] 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 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

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.

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

1. 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)

2. Drop the constraint:

   ALTER TABLE table_name DROP CONSTRAINT some_constraint
   

3. 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.

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.