Home / Community & Support / Developer Productivity Tools Forums / OpenAccess ORM > General Discussions > Insert statement conflicts with FK constraint

• Bastiaan

  Posted on Jan 2, 2012 (permalink)

  Evaluating OpenAccess, I created two tables on SQL Server 2008 R2: Master and Detail.
  
  Both have a primary key set which is also an identity. The Detail table contains an foreign key constraint on field MasterId to Id of the Master table.
  
  When I execute the following code, the exception below is thrown:
  
  using (EntitiesModel Model = new EntitiesModel())
              {
                  Master NewMaster = new Master();
                  NewMaster.Name = "Stane";
  
                  Detail NewDetail = new Detail();
                  NewDetail.Street = "Stane's new street";
  
                  NewMaster.Details.Add(NewDetail);
  
                  Model.Add(NewMaster);
                  Model.SaveChanges();
              }
  
  Insert of '1829379833-' failed: Telerik.OpenAccess.RT.sql.SQLException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Detail_Master". The conflict occurred in database "OATest", table "dbo.Master", column 'Id'.
  The statement has been terminated.
     at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
     at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
     at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
  INSERT INTO [Detail] ([MasterId], [Street]) VALUES (?, ?)
  select scope_identity()
  (set event logging to all to see parameter values) Telerik.OpenAccess.RT.sql.SQLException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Detail_Master". The conflict occurred in database "OATest", table "dbo.Master", column 'Id'.
  The statement has been terminated.
     at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
     at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
     at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
  
  
  Why is OpenAccess not handling the Id generation correctly?
  
  Kind regards,
  Bas
  
  

  Reply

• Henrik MVP

  Posted on Jan 3, 2012 (permalink)

  Hi Bas,
  
  Have you tried doing the Model.Add(NewMaster) just after initializing it like:
  
  Master NewMaster = new Master();
  Model.Add(NewMaster);
  
  Doing so will allow OA to handle change tracking of the added object.
  
  Also if the above do not help. Check in the visual designer that both the Master and the Detail classes have their key generation property set up correctly.
  
  Regards
  
  Henrik

  Reply

• Bastiaan

  Posted on Jan 3, 2012 (permalink)

  Hi Henrik,
  
  Thanks for the suggestion, that makes sense.
  
  I made the change to my code:
  
  

  Master NewMaster = new Master();

  Model.Add(NewMaster);

   

  NewMaster.Name = "Stane";

   

  Detail NewDetail = new Detail();

  NewDetail.Street = "Stane's new street";

   

  NewMaster.Details.Add(NewDetail);

   

  Model.SaveChanges(); // <-- exception

  
  
  Still get an exception, but it is different now:
  
  Insert of '1829379833-' failed: Telerik.OpenAccess.RT.sql.SQLException: Cannot insert the value NULL into column 'MasterId', table 'OATest.dbo.Detail'; column does not allow nulls. INSERT fails.
  The statement has been terminated.
     at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
     at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
     at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
  INSERT INTO [Detail] ([MasterId], [Street]) VALUES (?, ?)
  select scope_identity()
  (set event logging to all to see parameter values) Telerik.OpenAccess.RT.sql.SQLException: Cannot insert the value NULL into column 'MasterId', table 'OATest.dbo.Detail'; column does not allow nulls. INSERT fails.
  The statement has been terminated.
     at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
     at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
     at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
  
  
  I would expect that OA knows about this Id because of the association (FK constraint) between Master and Detail.
  
  I checked the key generation for both tables and the Identity Mechanism properties are set to "DatabaseServerCalculated". I think this is the correct setting (as per other forum posts) since Default, or HighLow require the voa_keygen table inside the database for internal tracking of Id's.
  
  
  

  Reply

• Henrik MVP

  Posted on Jan 3, 2012 (permalink)

  Hi Bas,
  
  Yes, this seems to be correct. This is a 1:m association right?
  Can you check how this is mapped. As you are having af FK column in the Detail table I am guessing this is a 1:m mapping without a join table.
  Please check if the association is "Managed". Per default all associations are unmanaged.
  
  If you select an association as to be "Managed" OA wil manage the relationsship regardless of how you add items to the list involved in the association.
  As a last resort try adding the Detail to the Context (Model) also.
  
  
  
  Regards
  
  Henrik

  Reply

• Bastiaan

  Posted on Jan 3, 2012 (permalink)

  The problem seemed to be the unmanaged navigation properties of both tables. Changing this to "true" immediately resolved the problem.
  
  Is there a specific reason why OA sets these by default to false? It seems to me that a relationship like this enforced with an FK constraint, using identity seeds on primary keys is one of the most common scenario's there is in the relational world.
  
  I am just wondering if there is any specific design consideration why this was implemented this way. Since this is also not very straightforward from the documentation (howto's and so on) it might put off potential customers whom do not take the time to post it in a forum :-)
  
  Anyway, thanks for your assistance.. it was really helpful.

  Reply

• Henrik MVP

  Posted on Jan 3, 2012 (permalink)

  Hi Bas,
  
  Glad I could help out.
  
  As for the design considerations for this being default unmanaged I am not sure, but I think I have read a post earlier, that this association management becomes quite expensive performance wise if you have deeply nested models with many (and I mean MANY) interrelated objects.
  Maybe someone from Telerik can elaborate on the exact reasons?
  
  Regards
  
  Henrik

  Reply

• Bastiaan

  Posted on Jan 3, 2012 (permalink)

  For very big domain models I would definitely expect to see a performance penalty if the hierarchy goes many levels deep, or if the collection of data contained within even a smaller object hierarchy is simply enormous.
  
  But I think these are likely to be more the exception than the rule.
  
  So by default I would expect the framework to configure itself according to best practices or most common use cases. Even if such big domain models are encountered, OA could make potential performance issues known during the design phase in one way or another (during the use of the wizard, compile time warnings, etc).
  
  I hope someone from Telerik will comment on the matter.
  
  

  Reply

• Bastiaan

  Posted on Jan 5, 2012 (permalink)

  Just did an "unmark as answer" so hopefully someone will notice the thread..
  

  Reply

• Ivailo Ivailo

  Posted on Jan 5, 2012 (permalink)

  Hello Bastian,

  Please accept my apologies for the late reply.
  
  The reason behind keeping the IsManaged to False by default is purely due to performance considerations. Our assumption is that you will not need it switched to True for all the cases, but only for the navigation properties you are planning to use in this way. For instance, you have a linkage between two classes, but you might be using it for read-only purposes only or for updating the data without changing the link (by adding/removing items from the collection). For this reason we decided that switching this value to True should be left to your decision, and for explicit setup - it is a pure defensive measure.
  
  You are right that the impact of this property is not very clear at the moment, so we will discuss possible ways to make it more obvious in order to prevent other clients from spending time on fighting with issues like the one you had. 
  
  Thank you for your input and do not hesitate to get back to us if you have further comments or ideas.
  
  

  All the best,
  Ivailo
  the Telerik team

  Q3’11 of Telerik OpenAccess ORM is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

  Reply

• Henrik MVP

  Posted on Jan 5, 2012 (permalink)

  Hello Ivailo,
  
  Thanks for clearing up the reasons behind this.
  
  I think it would be a nice idea to be very clear about this "Managed" collection being set to False by default.
  As a developer I would intuitive expect that if I add an element to a list the reverse reference is also handled for me... the opposite might be a discussion worth..
  While I can understand the reasons behind setting it to False, please be concise in docs about the impact.
  I have already answered a lot of threads regarding this issue in the forums.
  
  Regards
  
  Henrik

  Reply

• Ivailo Ivailo

  Posted on Jan 5, 2012 (permalink)

  Hi Henrik,

  You are right - we have noticed this trend as well. We will perform the necessary changes in documentation and probably even other client resources where applicable.
  
  Thanks for the input.
  
  

  Greetings,
  Ivailo
  the Telerik team

  Q3’11 of Telerik OpenAccess ORM is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

  Reply

Back to Top

Home / Community & Support / Developer Productivity Tools Forums / OpenAccess ORM > General Discussions > Insert statement conflicts with FK constraint