I have a parent table name A and there are 47 child table which are referencing to this parent table A, i want to delete records from 23 child tables and then the parent table as well, and want to keep the records in the remaining child tables.
I tried doing this by disabling and enabling constraint but faced issue while enabling the constraint.
Can anyone suggest any better approach.
NOTE: I am trying to achieve this via shell script.
Keyword here is ENABLE NOVALIDATE. It'll let you keep "invalid" existing data, but referential integrity will be enforced for newly added rows.
Here's an example.
Master and two detail tables:
SQL> create table master
2 (id_mas number primary key,
3 name varchar2(20));
Table created.
SQL> create table det_1
2 (id_det number primary key,
3 id_mas number constraint fk_d1_mas references master (id_mas),
4 name varchar2(20));
Table created.
SQL> create table det_2
2 (id_det number primary key,
3 id_mas number constraint fk_d2_mas references master (id_mas),
4 name varchar2(20));
Table created.
SQL>
Sample rows:
SQL> insert into master (id_mas, name)
2 select 1, 'Little' from dual union all
3 select 2, 'Foot' from dual;
2 rows created.
SQL> insert into det_1 (id_det, id_mas, name)
2 select 100, 1, 'Lit det 1' from dual union all
3 select 101, 1, 'Tle det 1' from dual union all
4 select 102, 2, 'Foot det 1' from dual;
3 rows created.
SQL> insert into det_2 (id_det, id_mas, name)
2 select 200, 1, 'Lit det 2' from dual union all
3 select 201, 2, 'Tle det 2' from dual union all
4 select 202, 2, 'Foot det 2' from dual;
3 rows created.
SQL> commit;
Commit complete.
SQL>
Now, let's delete ID_MAS = 1 from DET_1 and MASTER, but keep it in DET_2:
Deleting from detail table is OK (why wouldn't it be?):
SQL> delete from det_1 where id_mas = 1;
2 rows deleted.
I can't delete from master table because of foreign key constraint from DET_2:
SQL> delete from master where id_mas = 1;
delete from master where id_mas = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_D2_MAS) violated - child record found
So, let's disable it:
SQL> alter table det_2 disable constraint fk_d2_mas;
Table altered.
Deleting from master now succeeds:
SQL> delete from master where id_mas = 1;
1 row deleted.
Re-enabling previously disabled constraint on DET_2 will fail because it contains row(s) whose ID_MAS doesn't exist in MASTER table any more:
SQL> alter table det_2 enable constraint fk_d2_mas;
alter table det_2 enable constraint fk_d2_mas
*
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_D2_MAS) - parent keys not found
As I said, use ENABLE NOVALIDATE:
SQL> alter table det_2 enable novalidate constraint fk_d2_mas;
Table altered.
Tables' contents:
SQL> select * from master;
ID_MAS NAME
---------- --------------------
2 Foot
SQL> select * from det_1;
ID_DET ID_MAS NAME
---------- ---------- --------------------
102 2 Foot det 1
SQL> select * from det_2;
ID_DET ID_MAS NAME
---------- ---------- --------------------
200 1 Lit det 2 --> this master doesn't exist any more
201 2 Tle det 2
202 2 Foot det 2
SQL>
Let's try to insert some new (valid and invalid) rows:
SQL> insert into det_1 (id_det, id_mas, name)
2 select 110, 2, 'Valid' from dual;
1 row created.
SQL> insert into det_1 (id_det, id_mas, name)
2 select 111, 1, 'Invalid' from dual;
insert into det_1 (id_det, id_mas, name)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_D1_MAS) violated - parent key not
found
SQL> insert into det_2 (id_det, id_mas, name)
2 select 210, 2, 'Valid' from dual;
1 row created.
SQL> insert into det_2 (id_det, id_mas, name)
2 select 211, 1, 'Invalid' from dual;
insert into det_2 (id_det, id_mas, name)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_D2_MAS) violated - parent key not
found
SQL>
I am trying to achieve this via shell script.
A shell script? What does shell have to do with that? It is Oracle's business.