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.
Best Answer
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 to the table in common, regardless of what action is specified for the foreign key. Example:
Is there something in particular that prevents you from:
?