SQL 在 Oracle 中截断表出现错误

Truncate table in Oracle getting errors

提问by PPShein

I got the problem is when I run following command in Oracle, I encounter the error.

我遇到的问题是，当我在 Oracle 中运行以下命令时，遇到了错误。

Truncate table mytable;

Errors:

错误：

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

I found that, this mytable has relationship with other tables. That's why Truncate command cannot proceed anymore. How to delete data from myTable with the SQL scripts using Truncate command?

我发现，这个 mytable 与其他表有关系。这就是 Truncate 命令无法再继续的原因。如何使用 Truncate 命令使用 SQL 脚本从 myTable 中删除数据？

回答by RichardTheKiwi

You have to swap the TRUNCATE statement to DELETE statements, slower and logged but that's the way to do it when constraints are in place.

您必须将 TRUNCATE 语句交换为 DELETE 语句，速度较慢且已记录，但这是在存在约束时执行此操作的方法。

DELETE mytablename;

Either that or you can find the foreign keys that are referencing the table in question and disable them temporarily.

或者您可以找到引用相关表的外键并暂时禁用它们。

select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||';'
from user_constraints
where R_CONSTRAINT_NAME='<pk-of-table>';

Where pk-of-tableis the name of the primary key of the table being truncated

pk-of-table被截断的表的主键名在哪里

Run the output of the above query. When this has been done, remember to enable them again, just change DISABLE CONSTRAINTinto ENABLE CONSTRAINT

运行上述查询的输出。完成后，记得再次启用它们，只需更改DISABLE CONSTRAINT为ENABLE CONSTRAINT

回答by Najee Ghanim

this page offers a very good solution ...

这个页面提供了一个很好的解决方案......

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

ORA-02266: 表中的唯一键/主键被启用的外键引用

I'm here copying from it the Solution:

我在这里复制解决方案：

• Find the referenced ENABLED foreign key constraints and disable them.
• truncate/delete from the table .

• using any text editor .. just change disable to enable in the output you get from the query , then run it.

  select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'
  from all_constraints a, all_constraints b
  where a.constraint_type = 'R' and a.status='ENABLED'
  and a.r_constraint_name = b.constraint_name
  and a.r_owner  = b.owner
  and b.table_name = upper('YOUR_TABLE');
  

• 找到引用的 ENABLED 外键约束并禁用它们。
• 从表中截断/删除。

• 使用任何文本编辑器.. 只需在从查询获得的输出中更改禁用以启用，然后运行它。

  select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'
  from all_constraints a, all_constraints b
  where a.constraint_type = 'R' and a.status='ENABLED'
  and a.r_constraint_name = b.constraint_name
  and a.r_owner  = b.owner
  and b.table_name = upper('YOUR_TABLE');
  

回答by David Gelhar

The error message is telling you that there are other table(s) with a foreign key constraint referring to your table.

错误消息告诉您还有其他表具有引用您的表的外键约束。

According to the Oracle docs

根据Oracle 文档

You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table.

您不能截断已启用的外键约束的父表。您必须在截断表之前禁用约束。

The syntax for disabling a foreign keyis:

禁用外键的语法是：

ALTER TABLE table_name disable CONSTRAINT constraint_name;

ALTER TABLE table_name 禁用 CONSTRAINT 约束名；

回答by Srikant Patra

Issue:

问题：

Error “ORA-02266: unique/primary keys in table referenced by enabled foreign keys” when trying to truncate a table.

Error Message:

错误信息：

SQL> truncate table TABLE_NAME;  

truncate table TABLE_NAME
           *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Solution: -- Find the referenced foreign key constraints.

解决方案： -- 查找引用的外键约束。

 SQL> select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'
  2  from all_constraints a, all_constraints b
  3  where a.constraint_type = 'R'
  4  and a.r_constraint_name = b.constraint_name
  5  and a.r_owner  = b.owner
  6  and b.table_name = 'TABLE_NAME';

    'ALTER TABLE'||A.OWNER||'.'||A.TABLE_NAME||'DISABLE CONSTRAINT'||A.CONSTRAINT_NAME||';'
    ---------------------------------------------------------------------------------------------------------
    alter table SCHEMA_NAME.TABLE_NAME_ATTACHMENT disable constraint CONSTRAINT_NAME;    
alter table SCHEMA_NAME.TABLE_NAME_LOCATION disable constraint CONSTRAINT_NAME;

-- Disable them

-- 禁用它们

alter table SCHEMA_NAME.TABLE_NAME_ATTACHMENT disable constraint CONSTRAINT_NAME;
alter table SCHEMA_NAME.TABLE_NAME_LOCATION disable constraint CONSTRAINT_NAME;

-- Run the truncate

-- 运行截断

SQL> truncate table TABLE_NAME;

Table truncated.

-- Enable the foreign keys back

-- 重新启用外键

 SQL> select 'alter table '||a.owner||'.'||a.table_name||' enable constraint '||a.constraint_name||';'
  2  from all_constraints a, all_constraints b
  3  where a.constraint_type = 'R'
  4  and a.r_constraint_name = b.constraint_name
  5  and a.r_owner  = b.owner
  6  and b.table_name = 'TABLE_NAME';

'ALTER TABLE'||A.OWNER||'.'||A.TABLE_NAME||'ENABLE CONSTRAINT'||A.CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------

alter table SCHEMA_NAME.TABLE_NAME_ATTACHMENT enable constraint CONSTRAINT_NAME;
alter table SCHEMA_NAME.TABLE_NAME_LOCATION enable constraint CONSTRAINT_NAME;

-- Enable them

-- 启用它们

alter table SCHEMA_NAME.TABLE_NAME_ATTACHMENT enable constraint CONSTRAINT_NAME;
alter table SCHEMA_NAME.TABLE_NAME_LOCATION enable constraint CONSTRAINT_NAME;

回答by Lorenzo

Oracle 12c introduced a featureto truncate a table that is a parent of a referential integrity constraint having ON DELETE rule.

Oracle 12c引入了一个特性来截断一个表，该表是具有 ON DELETE 规则的参照完整性约束的父表。

Instead of truncate table tablename;use:

而不是truncate table tablename;使用：

TRUNCATE TABLE tablename CASCADE;

From Oracle truncate tabledocumentation:

来自 Oracletruncate table文档：

If you specify CASCADE, then Oracle Database truncates all child tables that reference table with an enabled ON DELETE CASCADE referential constraint. This is a recursive operation that will truncate all child tables, granchild tables, and so on, using the specified options.

如果您指定 CASCADE，则 Oracle 数据库会截断所有引用具有启用 ON DELETE CASCADE 引用约束的表的子表。这是一个递归操作，将使用指定的选项截断所有子表、子表等。

回答by Akhil

I had the similar issue and I sorted it out by the following scripts.

我遇到了类似的问题，并通过以下脚本对其进行了整理。

begin
for i in (select constraint_name, table_name from user_constraints a where a.owner='OWNER' and a.table_name not in
(select b.table_name from user_constraints b where b.table_name like '%BIN%') 
  and  a.constraint_type not in 'P')
LOOP
  execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
end loop;
end;
/

truncate table TABLE_1;
truncate table TABLE_2;


begin
for i in (select constraint_name, table_name from user_constraints a where a.owner='OWNER' and a.table_name not in
(select b.table_name from user_constraints b where b.table_name like '%BIN%') 
  and  a.constraint_type not in 'P')
LOOP
  execute immediate 'alter table '||i.table_name||' enable constraint '||i.constraint_name||'';
end loop;
end;
/

This script will first disable all the Constraints. Truncates the data in the tables and then enable the contraints.

此脚本将首先禁用所有约束。截断表中的数据，然后启用约束。

Hope it helps.

希望能帮助到你。

cheers..

干杯..

回答by plsql developer

TRUNCATE TABLE TEST2 DROP ALL STORAGE;

This statement Actually works when there is an foreign key constraint applied on a .table

当在 .table 上应用外键约束时，此语句实际上有效

回答by 9000

A typical approach to delete many rows with many constraints is as follows:

删除具有许多约束的许多行的典型方法如下：

• create mytable_newwith all the columns but without constrains (or create constraints disabled);
• copy whatever data you need from mytableto mytable_new.
• enable constraints on mytable_newto see that everything is ok.
• alter any constraints that reference mytableto reference mytable_newinstead and see that everything is ok.
• drop table mytable.
• alter table mytable_new rename to mytable.

• mytable_new使用所有列创建但没有约束（或禁用创建约束）；
• 将您需要的任何数据从 复制mytable到mytable_new.
• 启用约束mytable_new以查看一切正常。
• 更改引用mytable引用的任何约束，mytable_new并查看一切正常。
• drop table mytable.
• alter table mytable_new rename to mytable.

It's far faster than deleting a million records with many slow constraints.

这比删除具有许多缓慢约束的一百万条记录要快得多。

回答by Gan

As mentioned by the error message, you cannot truncate a table that is referenced by enabled foreign keys. If you really want to use the truncateDDL command, disable the foreign key constraint first, run the truncate command, and enable it back.

正如错误消息中提到的，您不能截断由启用的外键引用的表。如果确实要使用truncateDDL 命令，请先禁用外键约束，运行 truncate 命令，然后再启用它。

Reference: Difference between TRUNCATE, DELETE and DROP commands

参考：TRUNCATE、DELETE 和 DROP 命令之间的区别