Cascade Delete Relationship Question

[bwion]

I had a question about the cascade delete option when defining relationships
between tables. Say I have a table (Table 1) that is a one-to-many
relationship with another table (Table 2) based on standard primary/foriegn
key setup. I know if I set up a relationship between those tables and check
Cascade Delete then if I delete a record in Table 1 it will delete all the
records in Table 2 that are related.

However, Table 2 is also on the many "end" of a one-to-many relationship
between some lookup tables. I want to know if I delete a record in Table 1,
will it mess with the lookup tables that are connected to Table 2?

Ex:

Table 1:
CustomerID*
Name
Etc

Table 2:
TransationID
CustomerID
Qty
InventoryID
Etc

Table 3:
InventoryID
BarbieDollName
Etc

So CustomerID is the primary key in Table 1 (one-side) and foriegn key in
Table 2 (many-side). InventoryID is the primary key in Table 3 (one-side) and
the foriegn key in Table 2 (many-side). If I enforce referential integrity
with cascade delete on the CustomerID relations and I delete a record in
Table 1, will it just delete the corresponding records in Table 2 without
messing with Table 3?

Thanks in advance for the help.

Ben

 

[bwion]

Hey Steve,

Thanks a bunch. All makes sense.

Ben

 

[John W. Vinson/MVP]

I want to know if I delete a record in Table 1,
will it mess with the lookup tables that are connected to Table 2?

No. They will not be affected.

If Table2 had additional child tables, the deletion would fail if
*those* relationships had cascade deletes turned off, or it would
delete the "grandchild" records if it were on; but lookup tables won't
do anything.