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;
/
