SQL tip - Cascading deletes

Posted At : August 10, 2005 9:47 PM | Posted By : Trond Ulseth
Related Categories: Misc., ColdFusion

I just learned something new that saved me from pretty much work, so I thought I'd share.

I had the following situation; I'm making a Learning Management System in my daytime job. The system has clients (companies), which again have several users. And each user has several courses. I had made a DELETE method in a CRUD file for deleting clients, but naturally I also wanted the clients belonging to that company to be deleted, and each of the relation to these users courses to be deleted as well.

Now I was thinking, what is the way to do this that would a) make sense and logic in my model, and b) make use of as few queries as possible. I had a few ideas in mind which would avoid the queries within loops that I would have gone for in my earlier years of coding (very bad practice).

I mentioned the "problem" to a developer in a company sharing office space with us, and he said "Are you using a relational DB that supports cascading deletes". My reply was along the lines of "I have no idea what you mean, but I'm using MS SQL Server for this application". To make a short story even shorter, he showed me how to do cascading deletes in a MS SQL Server using Enterprise Manager.

If you are still reading I guess there's a chanse you don't know how to do this as well, so I'll explain. The idea is that if you have your relations set up corectly, whenever you delete a record - all records in a related table with that record as a foreign key will also be deleted. When you are inside a table in design view, right click and choose "Relationships...". If the relation is not defined from before you can also do that here (I'll not cover that here). Now check the choise for "Enforce relationship for INSERTs and UPDATEs", and then check the choise for "Cascade Delete Related Records".

By doing this both for the user/course relation and the client/user relation I could now just use the DELETE method in my client CRUD component, and the database would take care of "cleaning up" all the underlying "mess".

Excelent technique that already have, and will continue to save me a lot of work. However, I'd like to say: "Use with caution!"

Comments

[Add Comment]

Have you considered the legal implications of physically deleting your data?

At the uni I work for, I think we are required to keep records for 7 years or something like that.

# Posted By HKS | 8/10/05 11:32 PM

this is provided you have a normalised database with the correct constraints that allow cascading deletes. Thanx the crappy schema I have to play with I'm out of luck doing this.

moral of the story:

set the database up sensibly (eg: adding cascading delete constraints) and you'll save your developers heaps of time writing code.

my 2c

# Posted By barry.b | 8/11/05 12:26 AM

Can be useful but as barry talks about badly designed schema's, cascading deletes can be very lethal, leading to empty schemas...

# Posted By zac spitzer | 8/11/05 1:16 AM

HKS - in this case physically deleting the data is what I'm after. If not I would not have the DELETE function in the CRUD component in the first place. I'm also implementing a "history" table where I log created companies, created users, created courses, when courses are taken and the result of course tests, etc. - and this table will not be affected by this (or any other) DELETE methods. But, thanks for the warning anyway :)

Barry - yes this requires a well set up DB schema with the right relations set up, and as you say it saves heaps of time writing code - which was the moral of my story :)

Zac - this can indeed be lethal, thus my last words in the article was "Use with caution!" :)

# Posted By Trond Ulseth | 8/11/05 8:17 AM

[Add Comment]