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.