Violation of UNIQUE KEY constraint 'DF_CollectionKey_Caption'.
Identity Broker v5.2.1.0
When running an import on a connector, if you have a schema field in your connector that is the same as another connector or adapter, but only differing in casing, the import fails with the following error:
Connector processing failed. Connector Processing page 1 for connector Test2Csv failed with reason Violation of UNIQUE KEY constraint 'DF_CollectionKey_Caption'. Cannot insert duplicate key in object 'dbo.CollectionKey'. The duplicate key value is (MySecond). The statement has been terminated.. Duration: 00:00:00.0725432. Error details: System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'DF_CollectionKey_Caption'. Cannot insert duplicate key in object 'dbo.CollectionKey'. The duplicate key value is (MySecond). The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item) at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) at Unify.Product.IdentityBroker.Repository.EntityLinqQueryConverterUtilitiesBase`4.GetCollectionKeyData(TEntityKey key, EntityDataContext sourceContext) at Unify.Product.IdentityBroker.Repository.EntitySingleValueDataUtilityBase`2.CreateEntityValue(TEntityKey key, IValue value, IEntityCollectionKeyUtility`1 collectionKeyUtility, EntityDataSet set, __EntityInsertRow row, EntityDataContext sourceContext) at Unify.Product.IdentityBroker.Repository.KnownEntityContextBase`4.ConvertEntityValueToDataValue(KeyValuePair`2 entityValueAndKey, __EntityInsertRow row, EntityDataSet entityDataSet, EntityDataContext sourceContext) at Unify.Product.IdentityBroker.Repository.KnownEntityContextBase`4.<>c__DisplayClass31_0.<convertitemtovalues>b__0(KeyValuePair`2 entityValueAndKey) at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext() at System.Linq.Enumerable.<selectmanyiterator>d__17`2.MoveNext() at Unify.Framework.Visitor.Visit[T](IEnumerable`1 visitCollection, Action`2 visitor) at Unify.Product.IdentityBroker.Repository.KnownEntityContextBase`4.InsertItems(ISet`1 addedItems, EntityDataContext sourceContext, SqlConnection connection) at Unify.Framework.Data.LinqContextConversionBase`4.SubmitChanges() at Unify.Product.IdentityBroker.SaveChangedEntitiesTransformationUnit.Transform(IDictionaryTwoPassDifferenceReport`4 input) at Unify.Product.IdentityBroker.ConnectorEntityChangeProcessor.ProcessEntities(IEnumerable`1 connectorEntities, IEnumerable`1 repositoryEntities, IEntityChangesReportGenerator`2 reportGenerator) at Unify.Product.IdentityBroker.RepositoryChangeDetectionWorkerBase.PerformChangeDetectionOnConnectorEntityPage(IEnumerable`1 connectorEntities, Int32& index, Int32 entitiesProcessedSoFar, IEntityChangesReportGenerator`2 reportGenerator, IHashSet`1 seenKeys) at Unify.Product.IdentityBroker.RepositoryChangeDetectionWorkerBase.<>c__DisplayClass11_0.<performchangedetection>b__0(IEnumerable`1 page) at Unify.Framework.Visitor.ThreadsafeVisitorEvaluator`1.ThreadsafeItemEvaluator.Evaluate() ClientConnectionId:f57bfe7a-c01b-4101-87a7-e2809963b2e8 Error Number:2627,State:1,Class:14
To Reproduce:
Create two CSV connectors with duplicate schema, case sensitive. Run an import on both, recognise that the import succeeds.
Modify the schema of one of the connectors, changing only the casing of a field name. Re-run the import, and notice it fails with the exception above.
I've noticed this now across both the CSV connector and a custom connector. It also happens if you've got a field in the adapter with the same name, but differing in casing. You can test this by creating a constant field in one adapter that is the same as a unique schema field on your second connector, but only differing in casing. Run the connector import and the same error throws.
Answer
This issue is caused by the default SQL collation not matching that in code. Please use the workaround of changing field name to not clash.
If anyone comes up against this issue please let us know. Our current approach will be to add in support for new data layers, unless we get some new use cases.
The constraint was removed in v5.2.2 as it was causing more issues than it fixed.
I have just came across this error in version 5.2.1. Should I upgrade to version 5.2.2 or get the customer's 3rd party vendor to change their database view? I am thinking the former because the latter makes it sound like we have a bug.
Customer support service by UserEcho
This issue is caused by the default SQL collation not matching that in code. Please use the workaround of changing field name to not clash.
If anyone comes up against this issue please let us know. Our current approach will be to add in support for new data layers, unless we get some new use cases.