Re: PostgreSQL v11.3 - Records are deleted from child table if partition key of parent table is changed

On 2019-Jun-20, Rozboril, Robert wrote:

> ALTER TABLE polf ADD CONSTRAINT polf_po_id_fk FOREIGN KEY (po_id) REFERENCES pof(po_id) ON DELETE CASCADE;
> ALTER TABLE polt ADD CONSTRAINT polt_po_id_fk FOREIGN KEY (po_id) REFERENCES pot(po_id) ON DELETE CASCADE;

Hmm, yeah, I wouldn't expect this to work well -- the UPDATEs done by
the cascade action would not see that there is another partition. If
you were declaring the PK and FK constraints on the parent tablej, it
should work correctly:

ALTER TABLE pol ADD CONSTRAINT pol_pkey PRIMARY KEY (pol_id);
ALTER TABLE pol ADD CONSTRAINT pol_po_id_fk FOREIGN KEY (po_id) REFERENCES po(po_id) ON DELETE CASCADE;
(similarly for the "po" table).

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services