public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: Michael Corey <[email protected]>
Cc: [email protected]
Subject: Re: Foreign Key error between two partitioned tables
Date: Sat, 20 Apr 2024 11:19:47 +1200
Message-ID: <CAApHDvpnM7kbsm3omwxvkq6jSH+39DnpExm=9yV2QcutZipRow@mail.gmail.com> (raw)
In-Reply-To: <CAABu8T_ZZAFJykKFb-q3ZF8QWLDV8zXNP0jx7QPb6wWVE+_o2A@mail.gmail.com>
References: <CAABu8T_ZZAFJykKFb-q3ZF8QWLDV8zXNP0jx7QPb6wWVE+_o2A@mail.gmail.com>
On Fri, 19 Apr 2024 at 05:48, Michael Corey
<[email protected]> 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
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Foreign Key error between two partitioned tables
In-Reply-To: <CAApHDvpnM7kbsm3omwxvkq6jSH+39DnpExm=9yV2QcutZipRow@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox