What is causing ERROR there is no unique constraint matching given keys for referenced table

Question

Below example table structure gives an ERROR  there is no unique constraint matching given keys for referenced table  and having stared at it for while now I can t figure out why this error arises 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    Running the above code gives the following error  which does not make sense to me  can anyone explain why this error arises  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

User · Answer

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.

User · Answer

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

User · Answer

You should have name column as a unique constraint  here is a 3 lines of code to change your issues   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  Drop the constraint   ALTER TABLE table name DROP CONSTRAINT some constraint  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

User · Answer

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

[sql] What is causing ERROR: there is no unique constraint matching given keys for referenced table?

Examples related to sql

Examples related to postgresql