Postgresql 截断表与外键约束 [英] Postgresql truncate table with foreign key constraint

问题描述

目前我正在尝试在 Postgresql 11.3 上截断具有外键约束的表.

Currently I am trying to truncate tables which have foreign key constraint on Postgresql 11.3.

我试过这样做

BEGIN; 
SET CONSTRAINTS ALL DEFERRED;
TRUNCATE tableA;
COMMIT;

但收到错误

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "xxx" references "tableA".
HINT:  Truncate table "xxx" at the same time, or use TRUNCATE ... CASCADE.

SET CONSTRAINTS ALL DEFERRED 不是会关闭外键约束检查吗?无论如何，是否可以在不触发外键约束检查且不涉及 CASCADE 的情况下截断表?

Doesn't SET CONSTRAINTS ALL DEFERRED would turn off the foreign key constraint check? Are there anyway to truncate a table without triggering foreign key constraint check and not involving CASCADE?

推荐答案

从一张表中删除所有数据

TRUNCATE TABLE 语句的最简单形式如下:

The simplest form of the TRUNCATE TABLE statement is as follows:

TRUNCATE TABLE table_name;

从具有外键引用的表中删除所有数据

要从主表和所有具有主表外键引用的表中删除数据，请使用 CASCADE 选项，如下所示:

To remove data from the main table and all tables that have foreign key references to the main table, you use CASCADE option as follows:

TRUNCATE TABLE table_name CASCADE;

更新:

BEGIN;
ALTER TABLE table_name DISABLE TRIGGER ALL;
TRUNCATE TABLE table_name;
ALTER TABLE table_name ENABLE TRIGGER ALL;
COMMIT;

这篇关于Postgresql 截断表与外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！