locked
microsoft sync framework Foreign key violation RRS feed

  • Question

  • I am implementing the sync framework in my C# windows application.

    What I have is a table users (Lookup table), a table actions (Lookup table), and a table userActions which has a foreign key on both tables (when a user is assigned a certain action, it is saved in userActions).

    When I'm defining my scope in provisioning, the order is parent then child:

    DbSyncScopeDescription scopeDesc = .....
    
    DbSyncTableDescription users =
                    SqlSyncDescriptionBuilder.GetDescriptionForTable("Users", new SqlConnection(ServerConnection));
    
    DbSyncTableDescription userActions =
                    SqlSyncDescriptionBuilder.GetDescriptionForTable("UserActions", new SqlConnection(ServerConnection));
    
    userActions.Constraints.Add(new DbSyncForeignKeyConstraint("FK_UserActions_Users"));
    userActions.Constraints.Add(new DbSyncForeignKeyConstraint("FK_UserActions_Actions"));
    
    scopeDesc.Tables.Add(users);
    scopeDesc.Tables.Add(userActions);

    My sync should happen when a user is assigned a specific action (a specific record is inserted in userActions).

    So if I create a user 1 with action x, the user is synced. If I create a user 2 without action x, it won't be synced. so far so good.

    If now I assign action x to user 2 and re-apply sync, user 2 is still not being synced. what i noticed is that in my "ApplyChangeFailed" event, I am getting a foreign key violation exception:

    Local Provider apply change failed: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UserActions_Users". The conflict occurred in database "LocalGenUM", table "dbo.Users", column 'Id'.

    When i track the changes in event ApplyingChanges, in the dataset there is a record for the updated user, and records for his actions, but it seems like the sync is ignoring the user (since no changes are made on this table after first sync), and trying to insert in the userActions only (since a change happened on this table after sync) which is resulting in FK violation errors.

    Why is the sync ignoring the user? why is it forcing me to give the action to the user  immediately upon creation (before the sync takes place) ? can i trigger an insert for the parent when his child is being inserted? (i.e: when the sync is trying to insert a value in userActions and the parent User doesn't exist, force this user to be inserted)?

    I have been stuck with this for weeks. Can anyone help?

    Wednesday, December 10, 2014 2:23 PM

All replies

  • if your user has not changed, you shouldn't even be seeing an updated user on the change dataset in ApplyingChanges.

    can you post how you're skipping the insert of a new user without an action?

    can you also post your code in the ApplyChangesFailed event?

    Thursday, December 11, 2014 5:01 AM
  • So you're saying that everything i see in the Dataset is meant to be synced? Because this is not the case now. I see the user in the dataset, and i see the userActions, but the sync is only attempting to sync the userActions !

    I have nothing written in the ApplyChangesFailed except for this line

    e.Action = Microsoft.Synchronization.Data.ApplyAction.Continue;

    and an additional line to log the exception (which in my case is fk violation between table userActions and user).

    I am not skipping the insert of new user in my code. It is in my provisioning template that i specify the filters. In my case, my filter gets the users having action x (=> record in userAction table).

    So to put things straight:

    1. I create a user1 (table user), assign him action x => so in table userAction: user 1 - action x

    2. I create a user2 (table user) with no action => so in table userAction: no record for user2

    3. if i perform a sync at this point, in my Dataset (ApplyingChanges event): in table Users i see user1, and in table userActions i see user1 - action  x.

    4. I assign action x to user 2 => so now a new record is inserted in table userAction: user2 - action x .. no changes on table users.

    5. Perform sync now: I will see in the Dataset, in table Users: user2. and in table userAction: user2 - action x. but i am thrown to ApplyChangesFailed , where i am seeing the FK violation. Hence, the user is not synced to the destination DB nor his actions.

    in provisioning, the order of tables is: users, actions, userActions.

    DbSyncScopeDescription scopeDesc = .....

    DbSyncTableDescription users =

                    SqlSyncDescriptionBuilder.GetDescriptionForTable("Users", new SqlConnection(ServerConnection));

     DbSyncTableDescription Actions =                 SqlSyncDescriptionBuilder.GetDescriptionForTable("Actions", new SqlConnection(ServerConnection));

    DbSyncTableDescription userActions =

                    SqlSyncDescriptionBuilder.GetDescriptionForTable("UserActions", new SqlConnection(ServerConnection));

    userActions.Constraints.Add(new DbSyncForeignKeyConstraint("FK_UserActions_Users"));

    userActions.Constraints.Add(new DbSyncForeignKeyConstraint("FK_UserActions_Actions"));

    scopeDesc.Tables.Add(users);

    scopeDesc.Tables.Add(Actions); scopeDesc.Tables.Add(userActions);

     

    var serverTemplate = new SqlSyncScopeProvisioning(new SqlConnection(ServerConnection), scopeDesc, SqlSyncScopeProvisioningType.Template);            


    //user filter            

    serverTemplate.Tables["Users"].AddFilterColumn("Id");            

    serverTemplate.Tables["Users"].FilterParameters.Add(new System.Data.SqlClient.SqlParameter("@UserName", System.Data.SqlDbType.VarChar, 500));             

    serverTemplate.Tables["Users"].FilterClause = "[side].[Id] in (SELECT Users.ID FROM Users JOIN UserActions ON Users.id = UserActions.userid  WHERE UserActions.actionid = @actionID AND Users.BlaBla = @blabla)

     

    //userActions Filter            

    serverTemplate.Tables["UserActions"].AddFilterColumn("UserId");            

    serverTemplate.Tables["UserActions"].FilterParameters.Add(new System.Data.SqlClient.SqlParameter("@UserName", System.Data.SqlDbType.VarChar, 500));     serverTemplate.Tables["UserActions"].FilterClause = "[side].[UserId] in (SELECT Users.ID FROM Users JOIN UserActions ON Users.id = UserActions.userid  WHERE UserActions.actionid = @actionID AND Users.BlaBla = @blabla)

     

    what other info might you need?

    • Edited by NHarb Thursday, December 11, 2014 11:46 AM
    Thursday, December 11, 2014 11:44 AM
  • on your ApplyChangeFailed, i want you to log the actual conflict being thrown, not just the exception.
    Friday, December 12, 2014 6:33 AM
  • ok here's the conflict: (note that event ApplyChangeFailed is being fired twice)

    First time:
    - errorMessage: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UserActions_Users". The conflict occurred in database "LocalGenUM", table "dbo.Users", column 'Id'.
    The statement has been terminated.
    - LocalChange: null
    - RemoteChange: UserActions (which my updated record = user2-actionx)
    - Stage: ApplyingInserts
    - Type: ErrorsOccured

    Second Time:
    - errorMessage: null
    - LocalChange: null
    - RemoteChange: Users (which is the user i want = user2)
    - Stage: ApplyingUpdates
    - Type: LocalCleanedupDeleteRemoteUpdate
    Friday, December 12, 2014 4:26 PM
  • for your second conflict, set the action to RetryWithForceWrite. setting to Continue means you're skipping the conflict.

    Saturday, December 13, 2014 3:40 AM
  • Ok. But any idea why table UserActions is being synced before table Users ? Why is the sync attempting to insert UserActions then trying to Update Users? Knowing that in normal cases, when a user is created with his actions immediately, all works fine (users then userActions). Shouldn't the order of sync always be the same and depending on the order set in provision? Or does it apply the Inserts first, then the updates (UserActions is in the insert stage, and Users is in the update stage as indicated in conflict)?

    Monday, December 15, 2014 8:47 AM
  • Ok. I just remembered that the order of applying changes is Insert, Update then delete.

    Is there any way to always force table Users to be synced first regardless of whether it is an update or insert?

    Monday, December 15, 2014 10:14 AM