public inbox for [email protected]  

help / color / mirror / Atom feed

Foreign Key error between two partitioned tables
2+ messages / 2 participants
[nested] [flat]

* Foreign Key error between two partitioned tables
@ 2024-04-18 17:47  Michael Corey <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Michael Corey @ 2024-04-18 17:47 UTC (permalink / raw)
  To: [email protected]

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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Foreign Key error between two partitioned tables
@ 2024-04-19 23:19  David Rowley <[email protected]>
  parent: Michael Corey <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: David Rowley @ 2024-04-19 23:19 UTC (permalink / raw)
  To: Michael Corey <[email protected]>; +Cc: [email protected]

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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-04-19 23:19 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-18 17:47 Foreign Key error between two partitioned tables Michael Corey <[email protected]>
2024-04-19 23:19 ` David Rowley <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox