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

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


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?


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.