Hi,
I hope I didn't go over the top with this explanation :) On the other side, let me know if anything is unclear.
I got it wrong, the reason we cant use cascade deletes is because in
some of our tables, we have a foreign key for CompanyID and multiple
ContactID foreign keys. In our application we know, that the contactID
will only ever be contacts within the CompanyID selected, but SQL
doesnt like this, and throws an error when trying to do a cascade
delete.
What I meant by saying, "Don't just try to cascade everything, everywhere" is that developers tend to create a database that can
cascade as much as possible across every table because they feel that it will save them time later. I think that it just ends up
taking more time later to fix it :)
In other words, you probably don't need every possible cascade path in your database. If you are coding into your program the
ability for users, through the GUI or some other mechanism, to delete contacts then cascade from the Contacts table. If you need to
delete companies then cascade from the Company table.
If you absolutely need the ability to delete contacts and companies, independantly, then you'll have to analyze all of the possible
cascade paths:
The tables you've mentioned, but left nameless so I'll call one of them TableX, creates a many-to-many relationship and that always
seems to be the source of grief when trying to setup cascade paths. When you went through the tables and setup cascade paths you
must have created a cascade path from the Company table and the Contact table to TableX. At first this seems ok because it's
logical that if you delete either parent record that you'll want to delete the related records in TableX otherwise the referential
integrity of your data will be comprimised.
You've mentioned that you are also relating contacts to companies by a foreign key in contacts (IIRC from previous posts), which is
part of the circular refrence. Assuming that you have a foreign key in the contacts table named, "CompanyID", and that you have two
foreign keys in TableX named, "ContactID" and "CompanyID", then the following is true:
1. If you delete a record from Companies then you'll want all records in TableX to be deleted where TableX.CompanyID equals the
deleted Companies.CompanyID.
2. If you delete a record from Companies then you'll want all records from Contacts to be deleted where Contacts.CompanyID equals
the deleted Companies.CompanyID.
3. If you delete a record from Contacts then you'll want all records in TableX to be deleted where TableX.ContactID equals the
deleted Contacts.ContactID.
In this scenerio there is one choice that is better than the other in terms of the cascade paths that you can use. At first glance
you'd probably want to setup a cascade path on Companies to save yourself some coding since it acts as a primary key to more foreign
references than contacts, in this example. However, there are problems with that choice which I'll explain in a bit. A better
choice would be to CASCADE on TableX from Contacts and leave the foreign key reference of TableX.Company as NO ACTION. Now, when
you delete a contact from the database it will automatically delete all related records in TableX (solves #3).
However, if you try to delete a Company you'll get an error from SQL Server saying that the delete statement conflicts with foreign
key constraints in the Contacts table and TableX (although the error will probably only cite one table).
You can set the foreign key in Contacts to CASCADE on delete so that when you delete a Company it will automatically delete all
related contacts (solves #2). Unfortunately, you can't setup a cascading delete path on TableX to the Company table because that
would cause a circular reference since you already setup a cascading delete path on TableX from Contacts and Contacts references the
Company table. You can only have one path to TableX or the other. To solve #1 I recommend using an INSTEAD OF DELETE trigger on
the Companies table to maintain the referential integrity of your database. In the trigger delete all related records from TableX
and then delete the record in the Company table:
-- INSTEAD OF DELETE trigger on Company table:
DELETE FROM TableX WHERE CompanyID IN (SELECT CompanyID FROM deleted)
DELETE FROM Company WHERE CompanyID IN (SELECT CompanyID FROM deleted)
So why can't you cascade deletes from the Company table to TableX instead of from Contacts? Well, if you try to setup an INSTEAD OF
DELETE trigger on the Contacts table you'd find out that SQL Server won't let you because Contacts contains a foreign key reference
with on delete CASCADE from the Company table (used to solve #2). If you say, "well then I'll just remove that cascade path", you
actually end up creating more work for yourself. Here's what the trigger would look like:
-- INSTEAD OF DELETE trigger on Contacts table:
DELETE FROM TableX WHERE ContactID IN (SELECT ContactID FROM deleted)
DELETE FROM Contacts WHERE ContactID IN (SELECT ContactID FROM deleted)
But now, when you try to delete a record from the Company table SQL Server won't automatically delete all related records from the
Contacts table, since you removed cascading deletes in order to add the trigger, so you get an error saying that the delete
statement conflicts with the foreign key constraint in the Contacts table. Solution? Create a trigger on the Company table!
Hopefully it's clear that you can simply eliminate any need for a trigger on the Contacts table by handling cascading deletes from
the Company table to TableX through an INSTEAD OF DELETE trigger on the Company table, since either cascade path to TableX that you
choose will require a trigger on the Company table. (Albeit the body of the Company trigger will differ depending on the cascade
path you choose to TableX, but the trigger itself is still required by both)
Another scenerio that you've probably seen is the following: Let's say that, for instance, you have a Users table that has related
rows in other tables by an "UpdateUserID" column, for example, that keeps track of the last user that performs an update on any
given row. You might be tempted to create DELETE cascade paths from your Users table to every other related table, however it
wouldn't make sense to even delete users in the first place because their references will still be required to maintain the
historical records and relationships within the database. For instance, if you have a Company record with an UpdateUserID of 1 and
delete the User that has a UserID of 1, you wouldn't want the Company to be deleted as well but if you delete the user you'll have
to delete all related companies to preserve referential integrity. This is why it's better IMO to just add a bit flag to the Users
table and disable them if they are no longer needed by the application. It's important not to define cascade paths from the user
table so that SQL Server will raise an error if you try to delete a user that is being referenced by the Company table and so that
you don't inadvertantly delete required records.
I hope now you see that you must analyze all cascade paths in your database and find the ones that are appropriate for your
application. It's not necessarily all or none. Define the cascade paths that you will absolutely need so that you won't have to
explicitly enforce those relationships in code, and supplement the paths that SQL Server won't handle due to circular references by
creating INSTEAD OF triggers.
If i wanted to perform the deletions in code, is it best to put this
into the DAL or BLL?? For some reason i would say the BLL, but i am
unsure if this is correct. If i did do it in the DAL, then i would
have to reference other DataServices from within another DataService,
is this ok?
IMO it's best to enforce cascading in the RDBMS.
An example would be, if i was in the CompanyDataService, would it be ok
to make a call to the ContactDataService method DeleteByCompanyID??
Doing this i could perform a cascade delete, would this be advisable??
I wouldn't recommend it because it does break encapsulation, but that is just one perspective. Another perspective is that it would
be encapsulating the functionality required to delete all related records and it just so happens that it must rely on another
service to do its job. I don't like this perspective, however, because it seems to ignore the complexities that might arise if you
tried to enforce the relationships in your services that are already enforced by your RDBMS. I would say, let the database cascade
deletes and in a service just focus on deleting from the table of which that particular service is "aware". Your strong-typed
DataSets can also help to enforce table relationships in your services without having to explicitly code them.
--
Dave Sexton
