What is causing ERROR: there is no unique constraint matching given keys for referenced table?
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.
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.