How to perform Cascade Delete In Oracle database

I am using Oracle 10.2.  How do I set up the relationship between parent table and child tables so that when
record is deleted from the parent table it should also delete all the records in the child tables.

Thank you
jyemAsked:
Who is Participating?
 
riazpkConnect With a Mentor Commented:
while declaring the Foreign key, you will have to mention ON DELETE CASCADE option:

SQL>create table parent(a number primary key);

Table created.

SQL>ed
Wrote file afiedt.buf
  1* create table childe(a number, b varchar2(10), constraint child_fk foreign key(a)references parent on delete cascade)
SQL>/

Table created.

SQL>insert into parent values(1);

1 row created.

SQL>ed
Wrote file afiedt.buf

  1* insert into childe values(1, 'ABC')
SQL>/

1 row created.

SQL>commit;

Commit complete.

SQL>delete parent;

1 row deleted.

SQL>commit;

Commit complete.

SQL>SELECT * FROM parent;

no rows selected

SQL>ed
Wrote file afiedt.buf

  1* SELECT * FROM childe
SQL>/

no rows selected

SQL>
0
 
dqmqCommented:
0
 
jyemAuthor Commented:
I created a foreign key in the chilld table and link it to the pramary key in the parent table.  When deleting the record from the parent table I got the following error.

ORA-02292: integrity constraint (CBTM_OWN.CONSIST_DP_LOCO_R01) violated - child record found
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
cdemirCommented:
could you send table scripts
0
 
jyemAuthor Commented:
Thank you very much everyone.  Riazpk solution works.
0
 
ChipCommented:
Is there a script that can be run to identify what that one row will affect, if it is deleted?  I need to display all the tables that are dependant on that one record before deleting for documentation purposes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.