For a recent project I built a links application which had a parent/child relationship via one table:
CatID > ParentID.
The problem was if you deleted a link category - you would need to also delete any categories under that category (child) as well as any links associated with those categories.
Another negative was there was no way to know the depth of the parent/child relationship so it would take a complex loop to delete everything.
I remember reading something about using the database to do a ‘cascading’ delete so I dug into this and came up with the following solution which lets the database do the heavy work:
First I setup my table as such (notice tblLinkCategory relates to itself - tblLinkCategory_1)
The key to initially setting this relationship is the first record in tblLinkCategory MUST have a NULL value in parentID - since it’s the first record there is nothing to relate to - so a NULL must be used.
Next we just need to setup the cascade - right click on the relationship line between each table to get the properties:
Select “Enforce Referential Integrity” and “Cascade Delete Related Records”
Once you do this you can no longer delete rows with Access open (you will get a ‘table locked’ error) but it works fine via a delete query from ColdFusion.
Now by doing a single delete from the tblLinkCategory table:
delete
from tblLink
where id = <cfqueryparam value="#attributes.id#" cfsqltype="CF_SQL_INTEGER" maxlength="5">
It will delete the category, any sub-categories and any related links to those categories.
