Search code examples
sqlpostgresqlunique-constraintpgadmin-4postgresql-16

How can I avoid "ERROR: there is no unique constraint matching given keys for referenced table" on referencing combination primary key on Postgres?


I have the following table definition in the schema on pgAdmin4 I'm trying to execute:

CREATE TABLE IF NOT EXISTS Reporting
(
  SEQ integer,
  Product character varying(30) NOT NULL,
  Version integer NOT NULL,
  Grade character varying(30) NOT NULL,
  *Other_Detail...*,

  CONSTRAINT pk_Reporting
    PRIMARY KEY(SEQ, Product, Version, Grade),

  CONSTRAINT fk_Reporting_ProductGrade
    FOREIGN KEY(Product, Version, Grade)
       REFERENCES Product_Grade(Product, Version, Grade)
)

The table is supposed to reference this other table:

CREATE TABLE IF NOT EXISTS Product_Grade
(
   Product character varying(30) NOT NULL,
   Version integer NOT NULL,
   Grade character varying(30) NOT NULL,
   *Other_Detail...*,

   CONSTRAINT pk_ProductGrade
    PRIMARY KEY(Product, Version, Grade, Sampling Point)
)

The referenced table has its combination primary key identified and the child table has the foreign key constraint with subset of the combination primary key defined right, right?

How is it that Postgres throws out:

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

I had spelt out the foreign key constraints individually as well, but to no effect:

   CONSTRAINT fk_Reporting_ProductGrade1
       FOREIGN KEY(Product)
          REFERENCES Product_Grade(Product),
   CONSTRAINT fk_Reporting_ProductGrade2
       FOREIGN KEY(Version)
          REFERENCES Product_Grade(Version),
   CONSTRAINT fk_Reporting_ProductGrade3
       FOREIGN KEY(Grade)
          REFERENCES Product_Grade(Grade)

Edit: In the Product_Grade table definition's PK constraint, I had only 3 keys as the combination PK, but the table really has 4 keys:

  CONSTRAINT pk_ProductGrade
    PRIMARY KEY(Product, Version, Grade, **Sampling Point**)

Solution

  • Thanks to @Atmo and @Frank Heikens for their comments that guided me to adjust the script.

    I ended up adding a unique constraint with the 3 fields I wanted:

    CONSTRAINT unique_product_grade
        UNIQUE (product, version, grade)
    

    Then I was able to assign this combination as a foreign key:

    CONSTRAINT fk_reporting_productgrade
        FOREIGN KEY(product, version, grade)
           REFERENCES Product_Grade(product, version, grade)