Cascade deleting

[Melissa]

How can I make it so that when I want to delete a record
from a secondary table, it does not cascade delete the
main record from the primary table that it is related to?

It seems cascade delete will delete every record with teh
same primary id. is there anyway around that?

 

[Kevin3NF]

Cascade delete is from the top down, not from the bottom up...so...if you
delete an order, order details go with it. If you delete all the order
details, the order stays in place.

 

[John Vinson]

How can I make it so that when I want to delete a record
from a secondary table, it does not cascade delete the
main record from the primary table that it is related to?

ummm... Cascade Delete does *not* do this.

It will delete the "many" table records when you delete the record in
the "one" table, but deleting a record in a secondary table will not
affect the primary table.

How are you doing the delete? Are you perhaps unintentionally deleting
the main table record, for instance by deleting from a Query joining
the two tables?

 

[Tim Ferguson]

It seems cascade delete will delete every record with teh
same primary id. is there anyway around that?

As the others have pointed out, your terminology is the wrong way round,
but you may just be running up against the logic of your own business
rules.

Imagine an order-details system:

you can delete the order-detail 0019/02, and order 0019 will
not mind very much;

but if you delete the order 0019, then either the details
belonging to that order must be removed, or attached to
another order, or you just cannot be allowed to remove the
order.

On the other hand, imagine a patient-doctor system:

you can remove a patient without affecting the doctor

but if you remove a doctor, then you must (a) delete all
the patients belonging to him/ her; or (b) you must set all
the patients DoctorID fields to NULL (or another doctor); or
(c) you cannot remove the doctor record.

Either way, the point of relational integrity is to prevent an OrderNumber
or DoctorID pointing to a non-existent record. Sometimes Cascade Delete is
a convenience when removing records, but quite often it's a dangerous
liability.

Hope that helps

Tim F