Oracle 租户下含外键管理表做 truncate 报错 -5594

问题现象

对含有外键约束的主表进行 tuncate 操作时，报错 -5594, Cannot truncate a table referenced in a foreign key。

1. truncate 表操作。

   obclient [OBORACLE]> truncate table f1; 
   ORA-00600: internal error code, arguments: -5594, Cannot truncate a table referenced in a foreign key constraint (`OBORACLE`.`F2`, CONSTRAINT `FK_ID` FOREIGN KEY ("ID") REFERENCES `OBORACLE`.`F1` ("ID")) 
   

2. 禁用外键约束。

   obclient [OBORACLE]> alter table OBORACLE.F2 disable constraint FK_ID; 
   Query OK, 0 rows affected (0.056 sec) 
   

3. 再次 truncate 表操作。

    obclient [OBORACLE]> truncate table f1; 
    ORA-00600: internal error code, arguments: -5594, Cannot truncate a table referenced in a foreign key constraint (`OBORACLE`.`F2`, CONSTRAINT `FK_ID` FOREIGN KEY ("ID") REFERENCES `OBORACLE`.`F1` ("ID"))
   

适用版本

OceanBase 数据库 V2.x、V3.x 版本。

问题原因

在 OceanBase 数据库中，如果表为含有外键约束的主表，执行 truncate 表会报错，即便将外键 disable 也无效。

解决方法

在执行 DDL 之前先删除外键，执行完成后，重建外键。

1. truncate 表操作。

   obclient [OBORACLE]> truncate table f1;
   ORA-00600: internal error code, arguments: -5594, Cannot truncate a table referenced in a foreign key constraint (`OBORACLE`.`F2`, CONSTRAINT `FK_ID` FOREIGN KEY ("ID") REFERENCES `OBORACLE`.`F1` ("ID"))
   

2. 删除表上的外键约束。

   obclient [OBORACLE]> alter table OBORACLE.F2 drop constraint FK_ID;
   Query OK, 0 rows affected (0.087 sec)
   

3. 再次 truncate 表操作。

   obclient [OBORACLE]> truncate table f1;
   Query OK, 0 rows affected (0.235 sec)
   

4. 在表上添加原先删除的外键约束。

   obclient [OBORACLE]> alter table OBORACLE.F2 add constraint FK_ID foreign KEY (ID) references  OBORACLE.F1 (ID);
   Query OK, 0 rows affected (0.473 sec)
   

对于批量执行，可以用如下语句生成。

• 生成删除命令。

  obclient [OBORACLE]> select OWNER,constraint_name, 'alter table ' || t1.owner || '.' || t1.table_name || ' drop constraint '  
  || t1.constraint_name||';'  as sqltext from
  ALL_CONSTRAINTS t1   where t1.constraint_type = 'R'; 
  

  输出结果如下。

   +----------+-----------------+------------------------------------------------+
   | OWNER    | CONSTRAINT_NAME | SQLTEXT                                        |
   +----------+-----------------+------------------------------------------------+
   | OBORACLE | FK_ID           | alter table OBORACLE.F2 drop constraint FK_ID; |
   +----------+-----------------+------------------------------------------------+
  

• 生成创建命令。

  obclient [OBORACLE]> select v1.owner,v1.constraint_name,v1.sqltext||' '||t3.owner||'.'||t3.table_name||' (' ||
  listagg(t3.column_name, ',') within group(
  order by
  t3.position
  )||');' from (select
  t1.owner,
  t1.constraint_name,
  t1.table_name,
  t1.r_owner,
  t1.r_constraint_name,
  'alter table ' || t1.owner || '.' || t1.table_name || ' add constraint '  
  || t1.constraint_name || ' foreign KEY (' || listagg(t2.column_name, ',') within group(
  order by
  t2.position
  )||') references ' as sqltext
  from
  ALL_CONSTRAINTS t1 inner join all_cons_columns t2 on t1.owner = t2.owner and t1.constraint_name = t2.constraint_name
  where
  t1.constraint_type = 'R'
  group by
  t1.owner,
  t1.constraint_name,
  t1.table_name,
  t1.r_owner,
  t1.r_constraint_name)  v1 inner join all_cons_columns t3 on v1.r_owner = t3.owner  
  and v1.r_constraint_name = t3.constraint_name
  group by v1.owner,v1.constraint_name,v1.sqltext,t3.owner,t3.table_name;
  

  输出结果如下。

  +----------+-----------------+---------------------------------------------------------------------------------------------------------------------+
  | OWNER    | CONSTRAINT_NAME | V1.SQLTEXT||''||T3.OWNER||'.'||T3.TABLE_NAME||'('||LISTAGG(T3.COLUMN_NAME,',')WITHINGROUP(ORDERBYT3.POSITION)||');' |
  +----------+-----------------+---------------------------------------------------------------------------------------------------------------------+
  | OBORACLE | FK_ID           | alter table OBORACLE.F2 add constraint FK_ID foreign KEY (ID) references  OBORACLE.F1 (ID);                         |
  +----------+-----------------+---------------------------------------------------------------------------------------------------------------------+