Sample Header Ad - 728x90

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

7 votes
2 answers
3275 views
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.
Asked by Ryan Rapini (173 rep)
Mar 22, 2019, 10:25 AM
Last activity: May 18, 2023, 08:04 AM