Foreign Key error between two partitioned tables

From: Michael Corey <michael(dot)corey(dot)ap(at)nielsen(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Foreign Key error between two partitioned tables
Date: 2024-04-18 17:47:19
Message-ID: CAABu8T_ZZAFJykKFb-q3ZF8QWLDV8zXNP0jx7QPb6wWVE+_o2A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Version Postgres 13.13

Any help would be appreciated/

--
Michael Corey

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Buoro, John 2024-04-19 01:53:17 SSPI Feature Request
Previous Message Tom Lane 2024-04-18 14:53:37 Re: Why does it sort rows after a nested loop that uses already-sorted indexes?