On Tue, Dec 15, 2020 at 12:43 PM Amit Langote <[email protected]> wrote: > Quoting your original example: > > drop table a, b; > create table a (id serial, primary key (id)) partition by range (id); > create table b (id serial, primary key (id)) partition by range (id); > alter table b add constraint a_fk foreign key (id) references a (id) > on delete cascade; > create table a1 partition of a for values from (1) to (2); > create table a2 partition of a for values from (2) to (3); > create table b1 partition of b for values from (1) to (2); > create table b2 partition of b for values from (2) to (3); > insert into a (id) values (1); > insert into b (id) values (1); > > -- correctly errors out instead of silently performing the ON DELETE CASCADE > update a set id=2; > ERROR: update or delete on table "a" violates foreign key constraint > "a_fk" on table "b" > DETAIL: Key (id)=(1) is still referenced from table "b". > > select * from b; > id > ---- > 1 > (1 row) > > Changing the example to specify ON UPDATE CASCADE: > > drop table a, b; > create table a (id serial, primary key (id)) partition by range (id); > create table b (id serial, primary key (id)) partition by range (id); > alter table b add constraint a_fk foreign key (id) references a (id) > on delete cascade;
Oops, I copy-pasted the wrong block of text from my terminal. I meant: alter table b add constraint a_fk foreign key (id) references a (id) on delete cascade on update cascade; > create table a1 partition of a for values from (1) to (2); > create table a2 partition of a for values from (2) to (3); > create table b1 partition of b for values from (1) to (2); > create table b2 partition of b for values from (2) to (3); > insert into a (id) values (1); > insert into b (id) values (1); > > -- correctly applies ON UPDATE CASCADE action > update a set id=2; > UPDATE 1 > > select * from b; > id > ---- > 2 > (1 row) -- Amit Langote EDB: http://www.enterprisedb.com
