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
Last activity: May 18, 2023, 08:04 AM