Hi and greetings to all. First post here, been wading through the book trying to get my head round all the excellent concepts therein and have come up with my first n00b question :)
Not specifically CSLA-related but basically, what is the recommended way of dealing with the deletion of foreign keys. By this I mean the deletion of a database record to which there are foreign key references in other tables?
To give an example, in the Project Tracker application, if a Role was to be deleted from the Roles table, any Assignments which referenced this role would now hold an invalid foreign key reference. What's the recommended way of dealing with such situations?
A secondary issue is what happens if any business object instances reference this (now deleted) role? How are these dealt with?
TIA,
Toby.
Fair point. Taking each scenario in turn...
If the requirement is to prevent the deletion of a role which is "in use" then where would be the best place to handle this? Should the stored procedure do the checking and throw an exception if the role cannot be deleted? This would effectively be putting business logic in the database rather than the objects themselves, which would suggest handling the problem in the objects would be better, but I'm not sure how this would be done.
If the requirement is to remove all references to a deleted role then again, then frankly I can't see any way this could be done other than in the deleteRole stored procedure itself, unless I'm missing something.
Basically any way that I can see of doing this is horrible and clunky, I was just wondering if there's a more elegant and "accepted" way of handling this scenario of which I'm unaware.
Thanks.
Hi Vertigo
I usually just do the delete and catch exceptions in my override of Save and Delete methods.
All the systems I've made so far based on the CSLA framework, run on SQL Server, so I do not know if my method will work on other DBs.
Scenario 1:
If I have a scenario where I do not allow deletion of a parent record if it is referenced (in use), I do not set a cascading delete on the foreign key relation. In my code I still execute the delete by calling my delete sproc, however the referential integrity constraint on the foreign key relation will automatically throw an exception (sqlexception number 547) which I catch in my Save and Delete overrides. If I catch an exception of this type I know that a delete was tried and failed due to "in use".
Scenario 2:
If you want to allow deletion of child records, when deleting a parent record, you merely have to set a cascading delete on the foreign key relation. If you cannot do that, due to cycles, you will have to do the delete in your stored procedure.
/Henrik
Vertigo1:
Fair point. Taking each scenario in turn...
If the requirement is to prevent the deletion of a role which is "in use" then where would be the best place to handle this? Should the stored procedure do the checking and throw an exception if the role cannot be deleted? This would effectively be putting business logic in the database rather than the objects themselves, which would suggest handling the problem in the objects would be better, but I'm not sure how this would be done.
You should definitely enforce this in the database if you can. Otherwise, someone who does not understand the rules will trash your database with their own newly created/modified object. My motto is to never trust an application programmer when I can cheaply and efficiently enforce it in the database. I recognize that quite a few others in this audience may disagree. :) Strongly. :)
That doesn't mean you shouldn't check in your object code also - particularly if that would give a better user experience.
If the requirement is to remove all references to a deleted role then again, then frankly I can't see any way this could be done other than in the deleteRole stored procedure itself, unless I'm missing something.
Depending upon what is meant by "in use", a simple cascade delete option on a foreign key in the database could do the trick.
Stored procedure would be my choice. You can also try triggers or cascade delete feature of foreign keys (ORACLE).
If your table is large don't forget to create index on foreign key column. This improves referential integrety operation's perfromance.
If your table is large don't forget to create index on foreign key column. This improves referential integrety operation's perfromance.
Wow, I didn't know that. I use Sql Server 2005. What is your definition of large?
Thanks,
Mike
This is difficult to quantify. It depends on DB server power and parameters.
The best answer is when you feel the difference between table scan and index seek/index scan. I usually add this index regardless of table size to avoid future performance tuning exercises.
Sql Server supports the cascade delete feature on foreign keys also (with some limitations).
Triggers are also an option.
If the one stored procedure is the only one you will ever have that deletes records in that table, then the stored procedure can be fully trusted to do the job.
But, if you ever might want to have a BulkDelete stored procedure that deletes multiple records based upon some parameters passed in, it would also have to implement the same logic. Ditto for your PurgeDatabaseOfOldStuff procedure, etc., etc.
Cascade Delete FKs or Triggers will give you the most robust option when it comes to making sure the cascaded deletes happen the same way every time.
Copyright (c) Marimer LLC