When I first run
SET session_replication_role = DEFAULT; CREATE TABLE users ( id serial PRIMARY KEY ); CREATE TABLE posts( id serial PRIMARY KEY, user_id INTEGER REFERENCES users(id) );
And then run
INSERT INTO users(id) VALUES (1); INSERT INTO posts(id, user_id) VALUES (1, 1); SET session_replication_role = replica; DELETE FROM users;
It successfully deletes the row in
users even though this violates referential integrity because the
session_replication_role = replica. So far so good.
However, when I instead run
INSERT INTO users(id) VALUES (1); INSERT INTO posts(id, user_id) VALUES (1, 1); SET session_replication_role = replica; TRUNCATE users;
I get this output:
ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table "posts" references "users". HINT: Truncate table "posts" at the same time, or use TRUNCATE ... CASCADE.
For performance reasons, I really want to be able to use TRUNCATE and not DELETE. How can I get around this issue without using TRUNCATE users CASCADE? I can not use cascading deletes because my DB may have other foreign keys that reference the users table that I do not want to delete.