Oracle/PLSQL: ORA-02292 - Restricción de integridad violada - Registro secundario encontrado

A continuación vamos a mostrar con un caso práctico algunos posibles escenarios en los que se presentaría el error ORA-02292 y tres formas para evitarlo:

1. Vamos a tener dos tablas HR.PROVEEDOR (Tabla padre) y HR.PRODUCTO (Tabla hija)

CREATE TABLE HR.PROVEEDOR

( COD_PROV NUMERIC(10) NOT NULL,

  NOM_PROV varchar2(50) NOT NULL,

  NOM_CONT varchar2(50),

  CONSTRAINT PROVEEDOR_PK PRIMARY KEY (COD_PROV)

);

CREATE TABLE HR.PRODUCTO

( COD_PROD NUMERIC(10) NOT NULL,

  COD_PROV NUMERIC(10) NOT NULL,

  CONSTRAINT PROVEEDOR_FK

  FOREIGN KEY (COD_PROV)

  REFERENCES PROVEEDOR (COD_PROV)

);

2. Insertamos datos en las tablas:

INSERT INTO HR.PROVEEDOR (COD_PROV, NOM_PROV, NOM_CONT) VALUES (1000, 'MICROSOFT', 'DEAN VALDIVIA');

INSERT INTO HR.PRODUCTO (COD_PROD, COD_PROV)  VALUES (50000, 1000);

3. Si intentamos eliminar un registro:

delete from hr.proveedor where cod_prov = 1000; --- Nos aparecerá el mensaje de error ORA-02292

4. Si queremos deshabilitar las restricciones, con las siguientes consultas podemos ver el detalle:

select owner, constraint_name, constraint_type, table_name, status from dba_constraints; ó

select owner, constraint_name, constraint_type, table_name, status from user_constraints;

Para deshabilitar una restricción: ALTER TABLE table_name disable CONSTRAINT constraint_name;

Para habilitar una restricción: ALTER TABLE table_name enable CONSTRAINT constraint_name;

5. Para el ejemplo, si intentamos deshabilitar la Primary Key nos aparecerá el siguiente error: 

ORA-02297: cannot disable constraint (HR.PROVEEDOR_PK) - dependencies exist

Esto es porque en la tabla hija existe una Foreign Key se esta haciendo referencia al campo COD_PROV de la tabla HR.PROVEEDOR.

De igual forma si queremos eliminar la tabla que contiene la Primary Key nos aparecerá el siguiente error: ORA-02449: unique/primary keys in table referenced by foreign keys

6. Entonces para poder eliminar el registro COD_PROV = 1000, tenemos tres opciones:

6.1. Deshabilitar la Foreign Key

alter table hr.producto disable constraint proveedor_fk;

6.2. La Foreign Key tenga la opción ON DELETE CASCADE

• Eliminamos la Foreign Key existente: alter table hr.producto drop constraint proveedor_fk;
• Luego agregamos la Foreign Key con la opción "on delete cascade": alter table hr.producto add constraint proveedor_fk foreign key (cod_prov) references hr.proveedor(cod_prov) on delete cascade;

Ahora si eliminamos el registro: delete from hr.proveedor where cod_prov=1000, y automáticamente borrará el registro de la tabla padre e hijo.

6.3. La tercera posibilidad sería eliminar cod_prov=1000 en la tabla hija y luego en la tabla padre.

ADICIONALMENTE

Si optamos por deshabilitar todas las restricciones de un esquema de base de datos Oracle, podemos hacerlo con una de las tres formas siguientes:

Forma 1:

begin

for i in (select constraint_name, table_name from user_constraints) LOOP

execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';

end loop;

end;

/

Forma 2:

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints;

Forma 3:

BEGIN

  FOR c IN

  (SELECT c.owner, c.table_name, c.constraint_name

   FROM user_constraints c, user_tables t

   WHERE c.table_name = t.table_name

   AND c.status = 'ENABLED'

   ORDER BY c.constraint_type DESC)

  LOOP

    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name);

  END LOOP;

END;

/