-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: foreign key constraint fails on insert but data is there
PostPosted: Wed Apr 04, 2012 6:00 pm 
Newbie

Joined: Wed Apr 04, 2012 5:07 pm
Posts: 2
I have become completely stuck on an issue. Here's the scenario (using NHibernate 3.3.0 CR1 on an ASP.NET based website):

When a user is checking out on the site, as part of the final process it creates an order object and saves it to the database. Order items are saved along with the order. Then the application attempts to process the credit card. If the credit card authorization fails, it deletes the order and displays a validation failed message. If the user chooses to try again after correcting the info, an entirely new order object is created, saved and a new auto id is applied to it. If the user has chosen to have items shipped it creates a record in the appropriate shipment table for each item (UPS ship record, freight ship record, etc). The shipment record has the order item id as a foreign key linking the 1-1 relationship. This is where it blows up, but ONLY if an order was previously deleted!!?? Upon trying to save the shipment record (after the order and line items have been saved), it says that there isn't a line item id matching the one assigned to it. Now note that this process works perfectly if nothing is deleted every time. It only fails if an order is deleted and then you try to save a new one. It's the exact same code, and nothing is done differently whatsoever, so I know this has to have to do with NHibernate in some crazy form or fashion.

Everything is done inside a transaction, so I can't physically see the entries in the database at that time (it gets committed at the end of the request cycle), but by checking the properties on the objects I can see they have had id's successfully assigned to them. Since everything is using the same transaction they should all be within the same scope.

Below is the code where it fails. Note that the order and its items have already been saved at this point. The exact error message I get is: Cannot add or update a child row: a foreign key constraint fails (`tapinsulation`.`echoshipment`, CONSTRAINT `LineItemIdFK1` FOREIGN KEY (`LineItemId`) REFERENCES `lineitem` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Code:
                EchoShipment echoRecord = new EchoShipment(order, trackingNumber, plant, item, isPickup);
                EchoShipmentDAO EchoDAO = new EchoShipmentDAO();
               
               
                echoRecord= EchoDAO.Save(echoRecord);
                order.EchoShipments.Add(echoRecord);


Here's the pertinent section from the order object config:
Code:
<bag name="lineItems" access="field" lazy="false" cascade="all-delete-orphan"  >
      <key column="OrderId" />
      <one-to-many class="TapInsulation.Model.LineItem, TapInsulation.Model" />
    </bag>

    <bag name="upsShipments" access="field" lazy="false">
      <key column="OrderNumber" />
      <one-to-many class="TapInsulation.Model.UPSShipment, TapInsulation.Model" />
    </bag>

    <bag name="echoShipments" access="field" lazy="false">
      <key column="OrderNumber" />
      <one-to-many class="TapInsulation.Model.EchoShipment, TapInsulation.Model" />
    </bag>


and the pertinent section from the EchoShipment config:
Code:
<many-to-one name="LineItem" column="LineItemId"  />


Top
 Profile  
 
 Post subject: Re: foreign key constraint fails on insert but data is there
PostPosted: Thu Apr 05, 2012 11:54 am 
Newbie

Joined: Wed Apr 04, 2012 5:07 pm
Posts: 2
Well I figured it out.

So what was happening is that in the database it was set to automatically delete line items for an order when you delete the order. In the NHibernate config, it was set to also delete line items automatically via NHibernate when you delete an order. Well this shouldn’t have caused a problem, because I would think NHibernate would just first delete the line items before deleting the order, which should work just fine even though it was unnecessary. After simply changing the NHibernate config to not auto-delete line items it works perfectly now. Still doesn’t make any sense whatsoever, but that fixed it ugh. My only guess is that NHibernate was assuming there was no relationship setup in the database, deleting the order first, then trying to delete the line items afterwards. Since they weren’t there and nothing was deleted, it screwed up something in NHibernate’s memory. It never gave an error or anything though.

Can anyone explain the lack of logic behind this error? It seems pretty crappy that it was ever an issue in the first place and that I had to waste 10 hours for something as simple as a delete.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.