Re: Delete child-parent records???????

traineensp_at_softcell.co.in (Divya) wrote in message news:<c343a42.0306240317.3a15c354_at_posting.google.com>...
> Hi Mark,
> Thanks for your reply....
>
> I am trying to use the cascading command as under: -
> alter table commercial_details
> disable NOT NULL (commercial_details_id) CASCADE;
>
> But this is giving an error as i guess it is syntax wise
> wrong...So...if i want to delete commercial_details_id which lies in
> the parent table commercial_details as well as referenced ad a foreign
> key in delivery_details how do i go about using CASCADE? Tried
> refering a few sites but havent got it right tho'....
>
> I was told by few others that "Every delivery is associated with a
> commercial order ie in database there is reference to an commercial
> order id . Unless you delete that commercial order you cannot delete
> this delivery item you can only Edit it."
>
> Thanks and regards,
> Divya
> Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0306230523.dc251d5_at_posting.google.com>...
> > traineensp_at_softcell.co.in (Divya) wrote in message news:<c343a42.0306222019.6cd519c9_at_posting.google.com>...
> > > Hi,
> > >
> > > I am using Oracle 8.1.4.x and have few records already stored in few
> > > tables that share the referential integrity property....
> > >
> > > Now there is some major error in the frontend display for which i
> > > want to redo the entire display for which i need to first delete those
> > > records....But i am unable to delete hem freom either of those
> > > tables...I get the error message: -
> > >
> > > Error at Line 1:
> > > ORA 02292: integrity constraint (NSPAPP.sys_C006184)violated -
> > > child record found.
> > >
> > > Pls help me out...
> > > If i have emailed at the wrong zone pls guide me to the oracle newbie
> > > groups also then....
> > > Thanks in advance,
> > >
> > > Divya
> >
> > Disable the constraint, delete the rows, reenable the constraint.
> >
> > See the SQL manual for ALTER TABLE to see how to enable and disable
> > constraints. I would recommend you consider dropping and recreating
> > the constraints giving them names you choose rather than system
> > generated names as above.
> >
> > You can query dba_constraints to see all constraints defined against a
> > table. Foreign keys have a constraint_type of 'R'.
> >
> > HTH -- Mark D Powell --

You enable or disable the constraint name, See SQL manual section for the constraint clause. You set delete cascade or delete restrict at the time you create the FK. If delete cascase was set when you delete the parent the children will be deleted for you. On the otherhand if you are attempting to delete the parent and are getting an error then child rows exist and the FK constraint is definded as delete restrict.  Drop and re-create the constrain as delete cascade if that is how it should be set up. If the FK is currently valid and enabled then changing it should work. There is however a lot to be said for requiring the deliberate removal of children before removing the parent. With delete cascade you have a lot more to attempt to recover/fix if someone deletes the wrong parent.

If you disable the constraint Oracle will not check it till you attempt to re-enable it. At this point Oracle will attempt to validate the data. As long as you have cleaned up the data such that the associated rows exist and reference valid FK's it will re-enable.

The Oracle Concept manual has a short, very good section on constraints that may help you understand how the relationship should be set up for this data and what you need to do to make it that way.

HTH -- Mark D Powell -- Received on Wed Jun 25 2003 - 22:06:33 CEST