How to avoid Reference problems

[Raghuraman]

Hi,
I have some tables which have references between them.

If i delete the reors in the child tables , since it references the
column of the parent , i get error msg in my front end (quite
natural).

do u tell me how to delete the child table records with out affecting
the relation ships or can recreate the relations with out affecting my
data & structure and the constraints.

With thanks
Raghu

[Largo SQL Tools]

Re: How to avoid Reference problems

Raghu,

You can disable the constraint, delete the record(s) and then re-enable the
constraint.

J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available

http://www.largosqltools.com

"Raghuraman " <raghuraman_ace @rediffmail.com > wrote in message
news:66c7bef8.0 311210609.22491 e19@posting.goo gle.com...[color=blue]
> Hi,
> I have some tables which have references between them.
>
> If i delete the reors in the child tables , since it references the
> column of the parent , i get error msg in my front end (quite
> natural).
>
> do u tell me how to delete the child table records with out affecting
> the relation ships or can recreate the relations with out affecting my
> data & structure and the constraints.
>
> With thanks
> Raghu[/color]

[Lyndon Hills]

Re: How to avoid Reference problems

On 21 Nov 2003 06:09:54 -0800, raghuraman_ace@ rediffmail.com
(Raghuraman) wrote:
[color=blue]
>Hi,
>I have some tables which have references between them.
>
>If i delete the reors in the child tables , since it references the
>column of the parent , i get error msg in my front end (quite
>natural).
>
>do u tell me how to delete the child table records with out affecting
>the relation ships or can recreate the relations with out affecting my
>data & structure and the constraints.
>
>With thanks
>Raghu[/color]
You can usually delete child rows without problems. It's deleting a
parent that has children that is usually protected. One way is to
specify delete cascade when you create the reference. In this case
deleting the parent will delete all children.

For clarity, imagine two tables

OrderHeader
OrderNumber (PK),
Customer)

and

OrderDetail
(LineNumber,
OrderNumber (FK)
ProductCode)

Every row in OrderDetail has the key from OrderHeader as a foreign
key. You can delete the child rows from OrderDetail, but if you
attempt to delete from OrderHeader you will be stopped if there are
rows in OrderDetail, for that order. Specifying on delete cascade
means that the delete of OrderHeader also deletes all related rows in
OrderDetail.