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