Ensuring Database Integrity when Adding and Deleting

Question

As I am developing my database, I am working to ensure data integrity. So, for example, a Book should be deleted when its Author is deleted from the database (but not vice-versa), assuming one author.

When I setup the foreign-key, I did set up a CASCADE, so I feel like this should happen automatically if I perform a delete from LINQ. Is this true? If not, do I need to perform all the deletes on my own, or how is this accomplished?

My second question, which goes along with that, is: does the database ensure that I have all the appropriate information I need for a row when I add it to the table (e.g. I can't add a book that doesn't have an author), or do I need to ensure this myself in the business logic? What would happen if I did try to do this using LINQ to SQL? Would I get an exception?

Thanks for the help.

Answer 1

A cascading foreign key will cascade the delete automatically for you.

Referencial integrity will be enforced by the database; in this case, you should add the Author first and then the Book. If you violate referencial integrity, you will get an exception.

Answer 2

It sounds like for second question you may be interested in using a transaction. For example, you need to add several objects to the database and want to make sure all get added or none. This is what a database transaction accomplishes. And, yes you should do this in your data/business layer, you can do this by adding partial class to your datacontext classes. If your business process states that for example EVERY user MUST have ADDRESS or something to that nature. This is up to your case scenario.

LINQ automatically uses transactions provided you are within a single (using), i.e you perform everything in that one step.

If you need to perform multiple steps or combine with non LINQ database action then you can use the transaction scope. You need to enable DISTRIBUTED TRANSACTION SERVICE. This allows transactions across for example files and database.

See TransactionScope

using (TransactionScope scope = new TransactionScope())
{
do stuff here
scope.Complete
}