delete cascade [message #365663] |
Thu, 11 December 2008 20:56  |
Amulyadeep
Messages: 6 Registered: October 2008
|
Junior Member |
|
|
Hello experts,
when tried to delete a record from the table; i am getting an integrity constraint error. on the child table.
how to use delete cascade sql query in here.. can you please let me know
thank you
|
|
|
|
|
|
Re: delete cascade [message #366204 is a reply to message #365667] |
Fri, 12 December 2008 02:28  |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Amulyadeep wrote on Fri, 12 December 2008 05:14 | Thank you for the help!!!
|
If you followed ttparavindh's advice, you shall extra thank him for breaking your data integrity.
SQL> create table t1( c1 constraint pk_t1 primary key ) as
2 select 1 c1 from dual union all select 2 from dual;
Table created.
SQL> create table t2( c1 constraint pk_t2 primary key ) as
2 select 1 c1 from dual union all select 2 from dual;
Table created.
SQL> alter table t2 add constraint fk_t2 foreign key (c1) references t1(c1);
Table altered.
SQL> delete t1;
delete t1
*
ERROR at line 1:
ORA-02292: integrity constraint (FLYBOY.FK_T2) violated - child record found
SQL> alter table t1 drop constraint pk_t1 cascade;
Table altered.
SQL> delete t1;
2 rows deleted.
SQL> select * from t2;
C1
----------
1
2
SQL> alter table t2 add constraint fk_t2 foreign key (c1) references t1(c1);
alter table t2 add constraint fk_t2 foreign key (c1) references t1(c1)
*
ERROR at line 1:
ORA-02298: cannot validate (FLYBOY.FK_T2) - parent keys not found
SQL> insert into t1(c1) select 1 c1 from dual union all select 1 from dual;
2 rows created.
SQL> select * from t1;
C1
----------
1
1
The correct way would be deleting the child records before deleting the parent ones, or specifying the blocking foreign key constraint(s) as ON DELETE CASCADE (it has to be dropped and recreated with this option).
[Updated on: Fri, 12 December 2008 02:29] Report message to a moderator
|
|
|