Print Page | Close Window

Primary Key Constraints

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2918
Printed Date: 23-Apr-2025 at 4:15am


Topic: Primary Key Constraints
Posted By: prempos
Subject: Primary Key Constraints
Date Posted: 22-Aug-2011 at 5:58am
Hello,

We have a nagging problem that creeps up occasionally and is a show stopper in our production environment. Every so often, we notice a "primary key constraint" in the log file and all records after this error message do not get saved to the database. They do get preserved within the cache file, but if we exit our program then those records evaporate. We have backup mechanisms in place to where we can access each record and manually input each record back into the database, but our invoice numbers will differ once we up the primary key record and is causing a lot of pain for our customers in a live production environment.

I was told by my partner, that this constraint also used to come up with a negative "-1" value a year or so ago, and we were told by Ideablade support that it was an issue with Ideablade code and our code and the matter to my knowledge was never solved. To date we have seen this probably three to four times. We need a resolution to this issue fairly soon as our product is in a live production environment.

I would greatly appreciate any help with this matter, I will have a  snapshot of the debug log file soon and attach to this post.

Regards,

Pierre & kevin 


Replies:
Posted By: prempos
Date Posted: 22-Aug-2011 at 7:55am
error log:

Persistence Save Error: System.Data.OleDb.OleDbException: The statement has been terminated.
Violation of PRIMARY KEY constraint 'PK_ReceiptHeader'. Cannot insert duplicate key in object 'dbo.ReceiptHeader'.
   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
   at IdeaBlade.Persistence.Server.TransactionManagerHelper.SaveTable(EntityTable pTable, Boolean pDeleting, PostSaveHandler pPostSaveHandler, Boolean pExcludeFromRefetch)
   at IdeaBlade.Persistence.Server.TransactionManagerHelper.Save(DataSet pDataSet, SaveOptions pSaveOptions)

Posted By: kimj
Date Posted: 22-Aug-2011 at 2:03pm
I'd guess that the constraint violation is occurring because IDs with negative values have been written to the database.  I'd first check all database tables to see if this is the case.
 
As you know, negative IDs are used by DevForce as temporary IDs, and "real" non-negative IDs are generally assigned during save processing.  There are a few possible cases where a negative ID might be saved to the database:
  • If calling PM.SaveChanges with a partial list of entities but not providing the SaveOption of "InSaveListOnly".   DevForce will erroneously assume that the SaveOption is "All", and the IDs of any entities not included in the save will not ever be fixed up.  The workaround for this problem is to either pass the "InSaveListOnly" option when calling SaveChanges, or to do an unrestricted save for all modifications in cache.
  • Due to multi-threading issues, inconsistent IDs may be assigned to entities created and saved on the server. 
  • Some complex SaveEntitySet/RestoreEntitySet use cases can result in inconsistent IDs.
  • Incorrectly defined n-generation relationships (such as Grandparent - Parent - Child) can result in FK assignment problems, although in some models this could also result in bad PK assignments.  The fix for this is to ensure that all relationship possibilities have been mapped in the ORM.

If none of these sounds like a possible cause of your problem, can you also let me know whether you're using Identity IDs or a custom ID generator, and if Identities, if the UseSqlIdentityProc flag is on for the RdbKey.

Posted By: kjohnson
Date Posted: 22-Aug-2011 at 5:47pm
-We haven't set the SaveOption to 'InSaveListOnly' so that may be a possible fix.

-The project that is having the constraint violations is primarily single-thread, so I don't think that this would be an issue.

-Currently we are using the save/restore strategy demonstrated here: http://drc.ideablade.com/xwiki/bin/view/Documentation/code-sample-save-restore-chained-streams - http://drc.ideablade.com/xwiki/bin/view/Documentation/code-sample-save-restore-chained-streams . If there is a simpler more reliable method could you point me to a code sample?

-All of the relationships between our database objects should be properly defined, but I will review the relations between them just to be sure.

In terms on the ID generation, we are using IIdGenerator that is available in the 'Adding and Deleting Business Objects' Instructional Unit for DevForce Classic.

Posted By: kimj
Date Posted: 22-Aug-2011 at 6:22pm
I believe the "InSaveListOnly" option problem surfaces only with Identity columns, so also double check whether these tables are using Identities.  DevForce allows you to use both Identity columns and a custom ID generator, and it's the database definition of the column which determines which path DevForce takes (so you may not be using the IdGenerator you think you are).
 
I would also encourage you to check if any of your tables have negative ID values, since if not then none of this really applies.
 
The sample NumericIdGenerator is not thread safe for temporary ID generation.  If you are creating entities in the same PM on different threads within your client application, don't.  If you create new entities in your server-side code, for example in an RPC method or within any interception logic, then the ID generator will need to be made thread safe.  (We have a sample of this.)
 
The EntitySet save/restore, and ImportEntities too, can be an issue if new entities are involved, since they will receive a new temporary ID during these operations.  The actual use case is somewhat esoteric, but if you think it might apply we can look at it further.  How you do the save/restore does not matter, the issue is the content of the cache and the order in which entities were created.
 
 

Posted By: kjohnson
Date Posted: 23-Aug-2011 at 6:35am
Right now the only reason we are using multi-threading is for generating reports not for creating database objects. Currently the NumericIdGenerator is the only sample code I have seen for generating temporary IDs so if you can send me the link to the sample of the thread safe method I can take a look at it.

Posted By: kimj
Date Posted: 23-Aug-2011 at 9:38am
I sent the file to your email address.


Print Page | Close Window