sql - procedimiento - truncate table stored procedure

Truncar tabla en Oracle obteniendo errores

Question

Tengo el problema es que cuando ejecuto el siguiente comando en Oracle, encuentro el error.

Truncate table mytable;

Errores:

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

Encontré que, este mytable tiene relación con otras tablas. Es por eso que el comando Truncate ya no puede continuar. ¿Cómo eliminar datos de myTable con los scripts SQL usando el comando Truncate?

Answer 1

Como lo menciona el mensaje de error, no puede truncar una tabla a la que hacen referencia las claves foráneas habilitadas. Si realmente desea usar el comando truncate DDL, primero deshabilite la restricción de clave foránea, ejecute el comando truncar y habilítelo nuevamente.

Referencia: diferencia entre los comandos TRUNCATE, DELETE y DROP

Answer 2

Debe intercambiar la instrucción TRUNCATE por las instrucciones DELETE, más lentas y registradas, pero esa es la forma de hacerlo cuando existen restricciones.

DELETE mytablename;

O eso o puede encontrar las claves externas que hacen referencia a la tabla en cuestión y deshabilitarlas temporalmente.

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

Donde pk-of-table es el nombre de la clave principal de la tabla que se está truncando

Ejecutar la salida de la consulta anterior. Cuando se haya hecho esto, recuerde volver a habilitarlos, simplemente cambie DISABLE CONSTRAINT en ENABLE CONSTRAINT

Answer 3

El mensaje de error le indica que hay otras tablas con una restricción de clave externa que se refiere a su tabla.

Según los documentos de Oracle

No puede truncar la tabla principal de una restricción de clave foránea habilitada. Debe deshabilitar la restricción antes de truncar la tabla.

La sintaxis para deshabilitar una clave externa es:

ALTER TABLE table_name deshabilita CONSTRAINT constricción de nombre;

Answer 4

Esta página ofrece una muy buena solución ...

ORA-02266: claves únicas / primarias en la tabla a las que hacen referencia las claves externas habilitadas

Estoy aquí copiando la Solución:

• Busque las restricciones de clave foránea ENABLED a las que se hace referencia y deshabilítelas.
• Truncar / borrar de la tabla.

• usando cualquier editor de texto ... simplemente desactívelo para habilitarlo en la salida que obtiene de la consulta, luego ejecútelo.

  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'');

Answer 5

Oracle 12c introdujo una característica para truncar una tabla que es un elemento primario de una restricción de integridad referencial que tiene la regla ON DELETE.

En lugar de truncate table tablename; utilizar:

TRUNCATE TABLE tablename CASCADE;

Desde la documentación de la truncate table Oracle:

Si especifica CASCADE, la base de datos Oracle trunca todas las tablas secundarias que hacen referencia a la tabla con una restricción de referencia ON DELETE CASCADE habilitada. Esta es una operación recursiva que truncará todas las tablas secundarias, tablas granchild, etc., utilizando las opciones especificadas.

Answer 6

Problema:

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

Mensaje de error:

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

Solución: - Encuentre las restricciones de clave foránea referenciadas.

 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;

- Desactívalos

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

- Ejecutar el truncado

SQL> truncate table TABLE_NAME;

Table truncated.

- Habilitar las claves foráneas de nuevo.

 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;

- Habilítalos

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

Answer 7

Tuve un problema similar y lo resolví por los siguientes 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;
/

Este script primero deshabilitará todas las restricciones. Trunca los datos en las tablas y luego habilita los contraints.

Espero eso ayude.

aclamaciones..

Answer 8

Un enfoque típico para eliminar muchas filas con muchas restricciones es el siguiente:

• cree mytable_new con todas las columnas pero sin restricciones (o cree restricciones deshabilitadas);
• Copie los datos que necesite de mytable a mytable_new .
• habilite las restricciones en mytable_new para ver que todo está bien.
• modifique cualquier restricción que mytable referencia a mytable para que mytable referencia a mytable_new en mytable_new lugar y vea que todo está bien.
• drop table mytable .
• alter table mytable_new rename to mytable .

Es mucho más rápido que eliminar un millón de registros con muchas restricciones lentas.

Answer 9

TRUNCATE TABLE TEST2 DROP ALL STORAGE;

Esta declaración realmente funciona cuando hay una restricción de clave externa aplicada en una .table