|
|
|
|
Re: How to Delete Parent table record [message #560125 is a reply to message #560055] |
Tue, 10 July 2012 13:20 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
amitsukte wrote on Tue, 10 July 2012 05:42Is there any trick to this?..
The only thing you can do is:
a) Disable FK
b) Delete parent rows
3) Reenable FK with NOVALIDATE
For example:
SQL> create table parent as select level n from dual connect by level <= 5
2 /
Table created.
SQL> alter table parent add constraint parent_pk primary key(n)
2 /
Table altered.
SQL> create table child as select level n from dual connect by level <= 5
2 /
Table created.
SQL> alter table child add constraint child_fk1 foreign key(n) references parent
2 /
Table altered.
SQL> delete parent where n > 3
2 /
delete parent where n > 3
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.CHILD_FK1) violated - child record found
SQL> alter table child disable constraint child_fk1
2 /
Table altered.
SQL> delete parent where n > 3
2 /
2 rows deleted.
SQL> alter table child enable novalidate constraint child_fk1
2 /
Table altered.
SQL> select * from parent
2 /
N
----------
1
2
3
SQL> select * from child
2 /
N
----------
1
2
3
4
5
SQL> insert into child values(5)
2 /
insert into child values(5)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.CHILD_FK1) violated - parent key not found
SQL>
In general, this should be done when you are the only one working with child table to avoid invalid data coming into table while FK is disabled.
SY.
|
|
|
|
|
|
|
|