MIME-Version: 1.0
References: <CAABu8T_ZZAFJykKFb-q3ZF8QWLDV8zXNP0jx7QPb6wWVE+_o2A@mail.gmail.com>
In-Reply-To: <CAABu8T_ZZAFJykKFb-q3ZF8QWLDV8zXNP0jx7QPb6wWVE+_o2A@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Sat, 20 Apr 2024 11:19:47 +1200
Message-ID: <CAApHDvpnM7kbsm3omwxvkq6jSH+39DnpExm=9yV2QcutZipRow@mail.gmail.com>
Subject: Re: Foreign Key error between two partitioned tables
To: Michael Corey <michael.corey.ap@nielsen.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvpnM7kbsm3omwxvkq6jSH%2B39DnpExm%3D9yV2QcutZipRow%40mail.gmail.com>
Precedence: bulk

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