pls help with sql

[Nifrabar]

next scenario:

myData table with ID's

1
2
3

MM table with

myDataID myChildID
1 ------- 4
1 ------- 78
9 ------- 56
3 ------- 12
2 ------- 1
3 ------- 12

Q: I delete in myData records with ID 1 and 3
Is there an SQL approach to either delete corresponding records in MM tabel as well or make an updatable view holding all records with deleted parent?

TIA
-Bart

 

[Mike Lewis]

Hi Bart,

A couple of ideas come to mind.

First, if you want to write pure code:

lnIDToDelete = 1  && ID to delete from MyData
BEGIN TRANSACTION
DELETE FROM MM WHERE MyDataID = lnIDToDelete
DELETE FROM MyData WHERE ID = lnIDToDelete
END TRANSACTION

The reason for BEGIN / END TRANSACTION is, in case the second DELETE fails, you won't be left with orphaned records in the MM tabble.

Second option: Use the Referential Integrity Wizard:

1. MODIFY DATABASE <database name> -> Database menu -> Edit Referential Integrity

2. Select the relationship (the combination of parent and child tables).

3. Set "Rules for Deleting" to Cascade.

Does that answer your question, or is it more complicated than that?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Referential Integrity Rules can cascade a deletion of a parent record to child tables. VFP has a builder for that integrated, that comes available if you define relations via the keys in MODIFY DATABASE.

See

http://msdn.microsoft.com/en-us/library/aa975709(v=vs.71).aspx

Bye, Olaf.