Error: There Is No Unique Constraint Matching Given Keys For Referenced Table "Bar"

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

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?

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

The issue is this:

FOREIGN KEY (annee) REFERENCES inscription (annee)

In the inscription table you have:

PRIMARY KEY (annee, id_auditeur)

That qualifies as a unique constraint, but your FOREIGN KEY REFERENCE is only trying to match half the inscription PRIMARY KEY. To make it work try:

UPDATED

FOREIGN KEY (annee, id_auditer) REFERENCES inscription (annee, id_auditer)

Then you can rid of:

FOREIGN KEY (annee) REFERENCES inscription (annee)

there is no unique constraint matching given keys for referenced table PostgreSQL

All of these are in violation:

ALTER TABLE public."Job_Announcements"
    ADD FOREIGN KEY (city_name)
    REFERENCES public."Cities" (name)
    NOT VALID;

ALTER TABLE public."Job_Announcements"
    ADD FOREIGN KEY (role)
    REFERENCES public."Jobs" (role)
    NOT VALID;

ALTER TABLE public."Job_Announcements"
    ADD FOREIGN KEY (job_definition)
    REFERENCES public."Jobs" (definition)
    NOT VALID;

ALTER TABLE public."Activations"
    ADD FOREIGN KEY (email)
    REFERENCES public."Persons" (email)
    NOT VALID;

The manual:

A foreign key must reference columns that either are a primary key or form a unique constraint.

there is no unique constraint matching given keys for referenced table in SQL

You want a compound foreign key rather than two distinct keys:

CREATE TABLE STUDENT_REG (
    student_id INTEGER NOT NULL,
    course_number INTEGER NOT NULL,
    semester INTEGER NOT NULL, 
    FOREIGN KEY (student_id) REFERENCES STUDENT(student_id),
    FOREIGN KEY (course_number semester) REFERENCES TEST(course_number, semester)
);

Why you need that is because table TEST as has compound unique key on these two columns:

UNIQUE(course_number,semester)

So for table STUDENT_REG to unambigously refer to a row in TEST, you need the combination of both columns, which means a 2-columns foreign key rather than two distinct foreign keys.

Why am I getting there is no unique constraint matching given keys for referenced table... Error

Your table has to have unique index on the referenced column:

CREATE TABLE foo(
    id BIGSERIAL UNIQUE
);

How to fix error there is no unique constraint matching given keys for referenced table

You have two foreign keys in tb_register referencing round but only part of its key. Make that one referencing the complete key.

CREATE TABLE tb_register
             (...
              CONSTRAINT fk_register_round_number_discipline_id
                         FOREIGN KEY (round_number,
                                      discipline_id)
                                     REFERENCES tb_round
                                                (round_number,
                                                 discipline_id)
              ...);

---

Related Topics