I faced this problem while using NetTires which I highly recommend for anyone who wants to quickly generate a Data Access Layer using Microsoft Enterprise Library without having to do the grunt work and writing boilerplate Data Access Code.
In my efforts to not digress from the Topic I am going to keep some other data access mechanisms that I've used in the past out of the scope of this post and just state that NetTires is a Decent enough Data Access Mechanism I am currently in love with and am using it for a personal open source initiative. So, coming back to the point...
What's the problem?
"If you have two Tables having a Parent Child relationship bound by key which is an Identity Column Directly trying to Save the parent followed by the child in a single Transaction leads to a Foreign Key Violation Exception" - To Elaborate further I explain with an example. Consider the tables Student and Student Marks which are hooked to together into a one-to-many relationship by a StudentID which is an Identify Column.
So, if my application has a screen where a person enters student information and his marks at one single shot and I want to ensure that I either save the entire information entered (for both student and his marks) at one shot or nothing at all I would use transactions.
The steps involved would be typically open a transaction in ASP.NET (using NetTires in my case; or directly ADO.NET in other cases), save changes to Students table followed by saving changes to Student Exams table, followed by Committing the transaction.
So, what ever I've said till now "sounds" logical but it's not. The catch here is that I am using Identity Columns To identity a student and identity columns are generated by the database. So, assuming that this is the second student I am adding SQL Server generates Student ID as 2. The catch here is that there's no way to find out that 2 was Generated unless I've committed the transaction since the value 2 doesn't make it to the data base table unless the transaction is committed!
So, basically you can't save the child till you commit the parent and a transaction is not considered committed till both parent and child are successfully saved. A chicken-egg situation (somewhat).
And The Solution?
This is a classic ADO.NET problem (it's not even a problem, since it's been solved so many times before) and a lot of us might have addressed this in the past using this approach or some form of it. With NetTires the situation is a little difficult to track and fix specially when using DeepSave on the Parent (in our example Students) object.
A little bit of investigation into the nHibernate Generated stored procedures revealed that they were intelligently using SELECT SCOPE_IDENTITY. This means that a DeepSave on Student Table that was giving Foreign key violation exception can be fixed by:
- Not Using Transactions with Deep Save. This ensures that student is saved to DB and then StudentExam save is attempted. (Not really a solution, but a Band-aid - if you're looking for one :))
OR
- Using the Following Algorithm:
- Open A Transaction using a TransactionManager object;
- Call the Save Method of Student Entity by passing the same TransactionManager object (Don't Deep Save!);
- Once you do this the SELECT SCOPE_IDENTITY at the end of each generated stored procedure ensures that StudentID now has the new generated ID value even though the transaction hasn't committed!
- This Means that now it's possible to iterate through the collection of StudentExamsCollection available in the student object and do something like:
objStudent.StudentExamCollection[i].StudentID = objStudent.ID (inside the for loop that's iterating through these objects).
- Once this is done all your data is consistent. Call the Save Method of StudentExams table by passing the same TransactionManager object;
- Commit the transaction.
The whole Identity Column Vs. GUID's is a religious argument and this problem doesn't exist in the GUID world; but the GUID Vs. Identity Column argument for another post :) -
So, this post is my two cents, if you're using Identity Columns with connected tables and transactions, and are running into Foreign Key Constraints.