Cascading delete between two tables (1-to-1 related)

[vientito]

I am not too clear about why it happens



see following diagram:



My TBLItemID table is having several 1-to-1 related tables

I have enforced referential integrity and checked off Cascade delete related records

When I remove a record from TBLItemID the same row will be deleted from every one of the related tables

Yet when I go into any of the related tables and remove a row, via the 1-to-1 relationship I would expect that it will propagate through the whole web and remove the same row from every table. However that is not true. It will only remove its own record without touching any one else's.

How is cascading delete supposed to work? Does it work only when we remove records from parent table?

[ssanfu]

Even though the relationships are 1-to-1, there is still a "Parent" table and a "Child" table. With RI set and Cascade deleted enabled, if you delete a record from the "Parent" table, that record and all related records will be deleted. If a "Child" table has a record that is deleted, only that record will be deleted.

When you create the relationship(s), the first table selected becomes the "Parent" table. You selected "TBLitemID" first and linked the ID field to all other tables. Therefore, "TBLitemID" is the "Parent" table and all other tables are the "Child" tables.


Looking at your tables, you do not have a normalized table structure. I have never see 9 tables related in 1-to-1 relationships. I have very rarely seen any 1-to-1 relationships.

I would expect to see the "TBLitemID" table have 8 FK fields in 1-to-Many relationships.

"TBLAppDomains" has repeating fields. That is a sure sign of a non-normalized structure.


Good luck with your project. Unfortunately, I think you are going to have many headaches...