Skip to main content

ban-truncate-cascade

problem​

Using TRUNCATE's CASCADE option will truncate any tables that are also foreign-keyed to the specified tables.

So if you had tables with foreign-keys like:

a <- b <- c

and ran:

truncate a cascade;

You'd end up with a, b, & c all being truncated!

runnable example​

Setup:

create table a (
  a_id int primary key
);
create table b (
  b_id int primary key,
  a_id int,
  foreign key (a_id) references a(a_id)
);
create table c (
  c_id int primary key,
  b_id int,
  foreign key (b_id) references b(b_id)
);
insert into a (a_id) values (1), (2), (3);
insert into b (b_id, a_id) values (101, 1), (102, 2), (103, 3);
insert into c (c_id, b_id) values (1001, 101), (1002, 102), (1003, 103);

Then you run:

truncate a cascade;

Which outputs:

NOTICE:  truncate cascades to table "b"
NOTICE:  truncate cascades to table "c"

Query 1 OK: TRUNCATE TABLE

And now tables a, b, & c are empty!

solution​

Don't use the CASCADE option, instead manually specify the tables you want.

So if you just wanted tables a and b from the example above:

truncate a, b;