Geeks With Blogs
Rajiv Popat blog

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:

  1. 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
     
  2. Using the Following Algorithm:
    1. Open A Transaction using a TransactionManager object;
    2. Call the Save Method of Student Entity by passing the same TransactionManager object (Don't Deep Save!);
    3. 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!
    4. 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).
    5. Once this is done all your data is consistent. Call the Save Method of StudentExams table by passing the same TransactionManager object;
    6. 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.

Posted on Friday, September 8, 2006 1:18 PM Cookbooks And Articles , Quick Tips And Solutions | Back to top


Comments on this post: Transactions, Identity Columns and Foreign Key Constraint Errors - The Chicken Egg Situation

# re: Transactions, Identity Columns and Foreign Key Constraint Errors - The Chicken Egg Situation
Requesting Gravatar...
Thanks for the article and appreciate the effort. It's a tricky problem.

I am implementing a client side transaction which updates more than 10 tables.

This is what i plan to do
1)Initiate a transaction object in the application code
2) pass the transaction object to the methods of other tables
For the Parent key table, get the parent key and pass to the procedures of the child table.

3)if no errors, commit else rollback.

If i understand currently, the ID created for the Parent table can be passed to the child table within the same context as long you use scope_identity to return the Parent id value ?

Is that true ?

Please let me know.

Thanks
Left by rk on Dec 17, 2008 5:50 AM

# re: Transactions, Identity Columns and Foreign Key Constraint Errors - The Chicken Egg Situation
Requesting Gravatar...
Why would anyone use an identity column as a FK in another table when if a reseed takes place then there is a complete seperation of data between the two tables?
Left by Fred McCarter on May 11, 2009 5:54 AM

# re: Transactions, Identity Columns and Foreign Key Constraint Errors - The Chicken Egg Situation
Requesting Gravatar...
Kudos. This post was full of interesting stuff. I enjoyed reading it because it actually gives me a sense of satisfaction as a reader. :)
Left by Floating Laminate Floor on Jan 27, 2010 9:11 PM

# re: Transactions, Identity Columns and Foreign Key Constraint Errors - The Chicken Egg Situation
Requesting Gravatar...
I can’t help not to admire your post. It was definitely great and so interesting. I totally enjoyed it. Thank you so much for sharing and may you have a nice day as always.
Left by Mountain Cabins Rental on Apr 27, 2010 11:58 PM

# re: Transactions, Identity Columns and Foreign Key Constraint Errors - The Chicken Egg Situation
Requesting Gravatar...
I am looking forward for your next post on this topic.Your posts really do the help.Thank you! http://www.brandnew-shoes.com/ women's sandals to you!
Left by Welcome on Jun 11, 2010 3:09 AM

Your comment:
 (will show your gravatar)


Copyright © Rajiv Popat | Powered by: GeeksWithBlogs.net