Foreign Keys with ON DELETE SET NULL are still deleted when TRUNCATE CASCADE is called on the foreign table in Postgres

Maybe I am missing something here:

CREATE TABLE public.example_table (
id integer UNIQUE
);

CREATE TABLE public.foreign_table (
id integer,
example_table_id integer,
CONSTRAINT fk_example_table_id
FOREIGN KEY (example_table_id)
REFERENCES public.example_table (id)
ON DELETE SET NULL
);

INSERT INTO public.example_table (id) VALUES
(1);

INSERT INTO public.foreign_table (id, example_table_id) VALUES
(1, 1),
(2, null);


If I run TRUNCATE CASCADE, both tables are wiped which is not what I expected would happen.

TRUNCATE example_table CASCADE;

SELECT COUNT(*) FROM public.foreign_table;

0

What I would expect to happen would be that foreign_table would alter to:

(1, null)
(2, null)

Am I not understanding what SET NULL is supposed to accomplish?

Is there a way to use TRUNCATE CASCADE without having it delete from the other table? I use Laravel where I can call Model::truncate(); and it will automatically truncate the table and reset my indexes, I was hoping I could call this on the example_table and have it reset all the rows in foreign_table to null instead of just deleting the whole table.

Thanks for your help.

If I understand the documentation properly:

https://www.postgresql.org/docs/current/sql-truncate.html

TRUNCATE CASCADE truncates every table that has a foreign key relationship pointing to (referencing) the table in common, regardless of what action is specified for the foreign key.

Also note that the actions specified on foreign keys are ON DELETE and ON UPDATE. There is no option to define ON TRUNCATE action.

Example:

create table parent 
( x int not null primary key);

create table child 
( y int not null primary key
, x int not null references parent(x) 
                 on delete restrict 
                 on update restrict
);

insert into parent (x) values (1),(2),(3);
insert into child (y,x) values (1,1),(3,2);

truncate parent cascade;

select * from child;

There are no results to be displayed.

Is there something in particular that prevents you from:

delete from parent;

?

StackExchange Database Administrators Q#232828, answer score: 6