What causes ERROR: there is no unique restriction associated with the specified keys for the link table?

Question

The approximate table structure gives ERROR below: there is no unique restriction that matches the specified keys for the table that it refers to and looking at it, until I can understand why this error occurs 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; 

Executing the above code gives the following error, which does not make sense to me if anyone can explain why this error occurs. 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 

Answer 1

This is because the name column in the bar table does not have a UNIQUE constraint.

So, imagine that you have 2 rows in a bar table that contain the name 'ams' and you insert a row in baz from 'ams' to bar_fk , which row will refer to bar since there are two rows corresponding to it?

Answer 2

In postgresql, all foreign keys must refer to 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 in particular:

Finally, it should be noted that the foreign key must refer to columns that are either the primary key or form a unique constraint.

Emphasis on mine.

Answer 3

when you execute UNIQUE as a table-level constraint, as you did, then your definition is a bit like a composite primary key, see ddl limitations , here is an excerpt

 "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 any field can have a unique value if the combination is unique, and this does not match your foreign key constraint.

Most likely you want the restriction to be at the column level. therefore, instead define them as table-level restrictions, add UNIQUE at the end of the column definition, for example name VARCHAR(60) NOT NULL UNIQUE , or specify separate table level restrictions for each field.

Answer 4

You must have a column name as a unique constraint. here are 3 lines of code to change your problems

1. First find out the limitations of the primary key by typing this code

    \d table_name 

   you see like this below "some_constraint" PRIMARY KEY, btree (column)

2. Drop the restriction:

    ALTER TABLE table_name DROP CONSTRAINT some_constraint 

3. Add a new primary key column with the existing one:

    ALTER TABLE table_name ADD CONSTRAINT some_constraint PRIMARY KEY(COLUMN_NAME1,COLUMN_NAME2); 

All this.