none
The UPDATE statement conflicted with the FOREIGN KEY RRS feed

  • Question

  • Hello all,

    As in the title I have a little problem with my sync software. Let me explain :

    I have a client that will update the PK (xPK) of a table XTable. And the table YTable will have a FK which is the XTable's table PK. When doing my sync I get the error :

    The UPDATE statement conflicted with the FOREIGN KEY 
    constraint "FK_YTable_XTable". 
    The conflict occurred in database "DATABASE", 
    table "dbo.XTable", column 'xPK'.
    The statement has been terminated.

    I already searched everywhere for a solution but without any success.

    • The XTable is inserted first while declaring the scope
    • The "FK_YTable_XTable" is INSERT UPDATE CASCADE on both server and client.

    The following code is part of the server provision and client provision and sync :

    private void ProvisionServer()
            {
                try
                {
    
    
                    sServerConnection = Properties.Settings.Default["stringConn"].ToString();
                    SqlConnection serverConn = new SqlConnection(sServerConnection);
                    DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(sScope);
    
                    DbSyncTableDescription tableDesc1 = SqlSyncDescriptionBuilder.GetDescriptionForTable("SiteTable", serverConn);
                    DbSyncTableDescription tableDesc2 = SqlSyncDescriptionBuilder.GetDescriptionForTable("PlantTable", serverConn);
    
                    scopeDesc.Tables.Add(tableDesc1);
                    scopeDesc.Tables.Add(tableDesc2);
    
                    SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
                    serverProvision.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
                    serverProvision.Apply();
                    txtBoxLog.Text = txtBoxLog.Text + Environment.NewLine + ("Approvisionning server done.");
                    txtBoxLog.SelectionStart = txtBoxLog.TextLength;
                    txtBoxLog.ScrollToCaret();
                }
                catch (Exception ex)
                {
                    txtBoxLog.Text = txtBoxLog.Text + Environment.NewLine + ("ERROR: " + ex.Message);
                    txtBoxLog.SelectionStart = txtBoxLog.TextLength;
                    txtBoxLog.ScrollToCaret();
                }
    }
    
     private void ProvisionClient()
            {
                try
                {
                    SqlConnection clientConn = new SqlConnection("Initial Catalog = " + Properties.Settings.Default["DBName"] + ";Data Source = .\\SQLEXPRESS;Persist Security Info=True;Integrated Security = True;");
                    sServerConnection = Properties.Settings.Default["stringConn"].ToString();
                    SqlConnection serverConn = new SqlConnection(sServerConnection);
    
                    DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(sScope, serverConn);
                    SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
                    clientProvision.Apply();
                    txtBoxLog.Text = txtBoxLog.Text + Environment.NewLine + ("Approvisionning client done.");
                    txtBoxLog.SelectionStart = txtBoxLog.TextLength;
                    txtBoxLog.ScrollToCaret();
                }
                catch (Exception ex)
                {
                    txtBoxLog.Text = txtBoxLog.Text + Environment.NewLine + ("ERROR: " + ex.Message);
                    txtBoxLog.SelectionStart = txtBoxLog.TextLength;
                    txtBoxLog.ScrollToCaret();
                }
            }
    private void Sync()
            {
                try
                {sServerConnection = Thor_Sync.Properties.Settings.Default["stringConn"].ToString();
                    SqlConnection serverConn = new SqlConnection(sServerConnection);
    
                    SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
    
                    syncOrchestrator.LocalProvider = new SqlSyncProvider(sScope, clientConn);
                    syncOrchestrator.RemoteProvider = new SqlSyncProvider(sScope, serverConn);
    
                    syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;                ((SqlSyncProvider)syncOrchestrator.RemoteProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(RemoteProvider_ApplyChangeFailed);
                    ((SqlSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(LocalProvider_ApplyChangeFailed);
                    syncOrchestrator.SessionProgress += new EventHandler<SyncStagedProgressEventArgs>(syncOrchestrator_SessionProgress);
                    syncStats = syncOrchestrator.Synchronize();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
    

    Any help with this problem would be appreciated. Thank you very much.

    Tuesday, November 28, 2017 9:34 PM