Let assume that we have 2 tables, in a parent-child relationship, but the foreign key was created without "on delete cascade"; we want to run a few delete statements on the parent table, which should be cascaded to the child table and after the completion we want to restore the initial mode.
One way to do it is to delete from the child table first and then from the parent table, but this may pose a challenge, we only have conditions for delete for the parent table.
The solution will be re-create the existing foreign key with "on delete cascade", run all the delete statements and after that re-create the FK again, this time exactly as it was before.
Below is a small example:
SQL> create table parent (
2 col1_parent number not null);
Table created.
SQL> alter table parent add constraint parent_pk primary key (col1_parent);
Table altered.
SQL> insert into parent values(1);
1 row created.
SQL> insert into parent values(2);
1 row created.
SQL> insert into parent values(3);
1 row created.
SQL> create table child (
2 col1_child number,
3 FOREIGN KEY (col1_child) REFERENCES parent(col1_parent) );
Table created.
* To delete 1 row from the parent table, we first must delete from the child:
SQL> delete from parent where COL1_PARENT=1;
delete from parent where COL1_PARENT=1
*
ERROR at line 1:
ORA-02292: integrity constraint (FLORINM.SYS_C001881207) violated - child
record found
SQL> delete from child where col1_child=1;
1 row deleted.
SQL> delete from parent where COL1_PARENT=1;
1 row deleted.
* Assume we'd like to delete a few rows from parent, that we'll be cascaded to the child, as a one time thing only:
SQL> insert into child values(2);
1 row created.
SQL> insert into child values(3);
1 row created.
SQL> insert into parent values(10);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from parent;
COL1_PARENT
-----------
2
3
10
SQL> select * from child;
COL1_CHILD
----------
2
3
1* select table_name,constraint_name,constraint_type from user_constraints
SQL> i
2 where table_name in ('PARENT','CHILD');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
CHILD SYS_C001881207 R
PARENT SYS_C001881205 C
PARENT PARENT_PK P
SQL> alter table CHILD add constraint CHILD_FK foreign key (col1_child) references parent(col1_parent)
on delete cascade;
Table altered.
SQL> select * from parent;
COL1_PARENT
-----------
2
3
10
SQL> select * from child;
COL1_CHILD
----------
2
3
SQL> delete parent;
3 rows deleted.
SQL> select * from child;
no rows selected
SQL> alter table CHILD drop constraint CHILD_FK;
Table altered.
SQL> alter table CHILD add constraint CHILD_FK foreign key (col1_child) references parent(col1_parent);
Table altered.
