It's since the
name column on the
bar table does not have the UNIQUE constraint.
So conceive you have 2 rows on the
bar table that bearing the name
'ams' and you insert a row on
bar_fk, which row on
bar would it be referring because there are two rows matching?
In postgresql all foreign keys should reference a unique key in the parent table, thus in your
bar table you should have a
unique (name) index.
At the time you do
UNIQUE as a table level constraint as you have done then what your defining is a bit like a multiple 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 refers that either field could probably have a non unique value given the combination is unique and this does not match your foreign key constraint.
Possibly you want the constraint to be at column level. so rather then idebtify them as table level constraints, 'append'
UNIQUE to the end of the column definition like
name VARCHAR(60) NOT NULL UNIQUE or specify particular table level constraints for each field.
You must have name column as a unique constraint. here is a 3 lines of code to alter your issues
- First trace out the primary key constraints by typing this code
you are displayed like this at bottom
"some_constraint" PRIMARY KEY, btree (column)
Drop the constraint:
ALTER TABLE table_name DROP CONSTRAINT some_constraint
Include a new primary key column with existing one:
ALTER TABLE table_name ADD CONSTRAINT some_constraint PRIMARY KEY(COLUMN_NAME1,COLUMN_NAME2);
As the name column on the bar table does not have the UNIQUE constraint.
Let's presume you have 2 rows on the bar table that hold the name 'ams' and you are attempting to insert a row on baz with 'ams' on bar_fk, now which row on the bar would it be referring to because there are two rows matching can be taken care.