Home » SQL & PL/SQL » SQL & PL/SQL » delete cascade
delete cascade [message #365663] Thu, 11 December 2008 20:56 Go to next message
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 #365664 is a reply to message #365663] Thu, 11 December 2008 21:46 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


ORA-02292

Smile
Rajuvan.
Re: delete cascade [message #365665 is a reply to message #365663] Thu, 11 December 2008 21:50 Go to previous messageGo to next message
ttparavindh
Messages: 22
Registered: December 2006
Location: Bangalore,India
Junior Member

SQL> delete from dept;
delete from dept
*
ERROR at line 1:
ORA-02292: integrity constraint (ARAVINDH.DEPT_FK) violated - child record
found


SQL> alter table dept drop constraint pk_dept;
alter table dept drop constraint pk_dept
                                 *
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys


SQL> alter table dept drop constraint pk_dept cascade;

Table altered.

SQL> delete from dept;

4 rows deleted.


Regards.
icon14.gif  Re: delete cascade [message #365667 is a reply to message #365665] Thu, 11 December 2008 22:14 Go to previous messageGo to next message
Amulyadeep
Messages: 6
Registered: October 2008
Junior Member
Thank you for the help!!!
Re: delete cascade [message #366204 is a reply to message #365667] Fri, 12 December 2008 02:28 Go to previous message
flyboy
Messages: 1832
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

Previous Topic: Error Pl/SQL collection.
Next Topic: rename multiple columns
Goto Forum:
  


Current Time: Wed Dec 07 22:28:31 CST 2016

Total time taken to generate the page: 0.06283 seconds