Обсуждение: Foreign Key error between two partitioned tables
Hello,
I receive the following error when creating a foreign key between two partitioned tables.
ERROR: there is no unique constraint matching given keys for referenced table "par_log_file"
Here is my setup:
CREATE TABLE par_log_file (
par_file_id character varying(20) NOT NULL,
par_id character varying(64) NOT NULL
) PARTITION BY RANGE (par_file_id) ;
ALTER TABLE ONLY par_log_file
ADD CONSTRAINT plf_pk PRIMARY KEY (par_file_id);
--
CREATE TABLE par_log_definition (
par_file_id character varying(20) NOT NULL,
tif_seq_nmbr bigint NOT NULL,
name_25 character varying(255)
) PARTITION BY RANGE (par_file_id) ;
ALTER TABLE ONLY par_log_definition
ADD CONSTRAINT pld_pk PRIMARY KEY (par_file_id, tif_seq_nmbr);
CREATE INDEX pld_idx ON par_log_definition USING btree (par_file_id);
ALTER TABLE par_log_definition
ADD CONSTRAINT pld_fk FOREIGN KEY (par_file_id) REFERENCES par_log_file(par_file_id);
ERROR: there is no unique constraint matching given keys for referenced table "par_log_file"
Here is my setup:
CREATE TABLE par_log_file (
par_file_id character varying(20) NOT NULL,
par_id character varying(64) NOT NULL
) PARTITION BY RANGE (par_file_id) ;
ALTER TABLE ONLY par_log_file
ADD CONSTRAINT plf_pk PRIMARY KEY (par_file_id);
--
CREATE TABLE par_log_definition (
par_file_id character varying(20) NOT NULL,
tif_seq_nmbr bigint NOT NULL,
name_25 character varying(255)
) PARTITION BY RANGE (par_file_id) ;
ALTER TABLE ONLY par_log_definition
ADD CONSTRAINT pld_pk PRIMARY KEY (par_file_id, tif_seq_nmbr);
CREATE INDEX pld_idx ON par_log_definition USING btree (par_file_id);
ALTER TABLE par_log_definition
ADD CONSTRAINT pld_fk FOREIGN KEY (par_file_id) REFERENCES par_log_file(par_file_id);
Version Postgres 13.13
Any help would be appreciated/
Michael Corey
On Fri, 19 Apr 2024 at 05:48, Michael Corey <michael.corey.ap@nielsen.com> wrote: > ALTER TABLE ONLY par_log_file > ADD CONSTRAINT plf_pk PRIMARY KEY (par_file_id); > ALTER TABLE par_log_definition > ADD CONSTRAINT pld_fk FOREIGN KEY (par_file_id) REFERENCES par_log_file(par_file_id); > I receive the following error when creating a foreign key between two partitioned tables. > ERROR: there is no unique constraint matching given keys for referenced table "par_log_file" > Version Postgres 13.13 No problems running that script here on 13.13. I imagine you've probably got a partition attached to par_log_file already and since your "plf_pk" constraint is on ONLY par_log_file, then the supporting index is likely invalid. I'd suggest checking if this is the case with: select indexrelid::regclass,indisvalid from pg_index where indrelid = 'par_log_file'::regclass; The correct way to create the PK constraint is with: ALTER TABLE par_log_file ADD CONSTRAINT plf_pk PRIMARY KEY (par_file_id); You might want to create supporting unique indexes on each partition CONCURRENTLY before doing that so that the ALTER TABLE becomes a meta-data-only operation. David