What is causing error “ There is no unique constraint matching specific keys for referenced table?

300    Asked by GraceDuncan in Devops , Asked on Jul 25, 2023

The example below hows the error: there is no unique constraint matching speicifc keys for the referenced table, and now I cant understand why the error occurs in this case.

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 code mentioned above shows the error. Can anybody tell the reason for the error.

Answered by Darsh K

When the query references a column that do not possess the UNIQUE constraint, you will receive the error “There is no unique constraint matching specific keys for referenced table”. It is very necessary in PostgreSQL, that the foreign key references columns that is a primary key or a unique constraint. Since the name column on the bar table do not possess the UNIQUE constraint, let us suppose you have 2 rows in the bar table that comprise the name “ams” and you are attempting to enter a row on bar with “ams” on bar_fk, which row on the bar would it refer to as there are two rows compatible can be looked after.


DevOps certification training at JanBask offer an extensive training on the core concepts of devOps. The company aims at imparting the skills that turns you job-ready to face the market scenario. JanBask also lets you grasp the concepts from your home without having to travel to a distant location.


Your Answer

Interviews

Parent Categories