7

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.

3
  • Well, I don't have answer to your whole question but when you are using CASCADE you are purging all the data in tables which are referenced to table on which you are executing TRUNCATE. So, you don't have bad understanding on SET NULL. You are purging every single data on table where you want purge + on every single table which is referenced to that one. Mar 22, 2019 at 10:38
  • 2
    "Which is not what I expected" - but which is clearly documented in the manual: "Automatically truncate all tables that have foreign-key references to any of the named tables*"
    – user1822
    Mar 22, 2019 at 11:18
  • @a_horse_with_no_name I read that in the documentation, but my point is that I assumed "ON DELETE SET NULL" would modify this behavior. If we have a foreign-key reference and it is set to NULL it would seem like it no longer exists and therefore no longer needs to be truncated. But clearly TRUNCATE works at the table level, not at the row level. Mar 22, 2019 at 17:49

2 Answers 2

6

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;

?

2
  • 2
    I agree, it seems like this is the behavior, but it wasnt what I expected. I will use delete instead. Mar 22, 2019 at 11:19
  • 1
    nothing like having this problem, only to find this answer and see that you've up-voted it years back. oh well that's what backups are for Apr 2, 2023 at 0:10
1

The same case happens in Django (Python), Truncate Cascade delete reference record as well instead of set NULL policy.

If we set protect then it won't be deleted even Truncate Cascade event trigger, but I expected to set NULL while deleting the parent record instead of deleting the reference too.

enter image description here This is a simple case, If want to delete the user with cascade then order table is protected, history is a cascade, and the movie is set null while deleting, then what will be the expected result.

Using PG Admin, they have 2 options Truncate, and Truncate Cascade and the result was unexpected, deleted all reference record.

1
  • If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review
    – mustaccio
    Jan 26, 2023 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.