ERROR: there is no unique constraint matching given keys for referenced table "bar"

user_438319 0
Viewed 284074

Trying to create this example table structure in Postgres 9.1:

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)
);

Running the above code produces an error, which does not make sense to me:

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

Can anyone explain why this error arises?

unique-constraint postgresql foreign-keys sql
4 Answers

You should have name column as a unique constraint. here is a 3 lines of code to change your issues

  1. 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)

  2. Drop the constraint:

    ALTER TABLE table_name DROP CONSTRAINT some_constraint

  3. 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_91681070
Related