1

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.

  • 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. – Superbrain_bug Mar 22 at 10:38
  • "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*" – a_horse_with_no_name Mar 22 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. – Ryan Rapini Mar 22 at 17:49
1

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:

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;

?

  • I agree, it seems like this is the behavior, but it wasnt what I expected. I will use delete instead. – Ryan Rapini Mar 22 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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