Self-referencing Foreign Keys

mardi 19 août 2008 17:12

0
On initial sync I have run into problems with self-referencing tables. I am syncing between MS SQL Server 2005 and SQLExpress 2005 using a custom sync provider.

I can always drop the constraints if necessary, but on initial sync into an existing schema that already has the constraints in place, those inserts fail.

Is there a way to guarantee that the parent in the relationship is inserted first?

Here's a simplified example of what I'm trying to accomplish:

Employee Table

ID                                     Name         ManagerID

3f14327a-f921-402b-b33d-bcea8c1bb221   Employee1    202b598a-f821-4f82-98e5-7440d70e367a

202b598a-f821-4f82-98e5-7440d70e367a   Employee2    Null

75e6396f-0b17-4333-96b7-47282b30099a   Employee3    Null

I imagine the only way to fix this would be to correct the main problem with data coming over from a conversion being entered when there are no constraints on the table.

I was going to just order the data by the ManagerID to insert the null ManagerIDs first, but there are at least 1 additional column with a foreign key constraint against the ID column.
- Déplacé Max Wang_Chinasoft mercredi 20 avril 2011 00:14 Forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])

Toutes les réponses

mercredi 20 août 2008 13:28

0

So far it looks like the easiest way to handle it is just use the ApplyChangeFailed event and RetryWithForceWrite.

In the T-SQL for the insert for retry with force write, I just disable table constraints and re-enable after the insert. It's probably the best way to handle it.

If anyone else has any thoughts, let me know.
vendredi 22 août 2008 08:32

Modérateur

0

if you can disable the table constraint and reenable it after the sync, then you may consider to disable it even before the applyChange occurs, this will avoid the applyCHangeFailed event to be fired and rows reinserted. --- better performance.

just curious, are you using peer to peer sync ?

thanks

Yunwen
vendredi 22 août 2008 10:32

0

Thanks for the help. That's what I'll do.

I just pulled up the Collaboration Scenarios section on peer-to-peer sync in the docs and must have never noticed it before. I'll have to check it out today.
vendredi 22 août 2008 14:11

Modérateur

0

I thought that it might make sense to add provide some info here. The Sync Framework guarantees that tables will be synchronized in the order you specify within the sync adapter collection which, in turn, avoids violating foreign key constraint violations. However, we do not grantee the ordering of rows within a table which has the potential to violate self-referencing FK constraints. It is certainly debatable as to whether or not we should. Note that this ordering becomes extremely complex if I have more than one self-referencing constraint on the base table. As Yunwen noted, the best practice would be to drop your self-referencing FK constraint priori to applying changes for each table so that it is only dropped and recreated once. Feedback here is welcome.

Sean Kelley

Program Manager

Microsoft
vendredi 22 août 2008 16:07

0

Absolutely, I was doing this in the ApplyChangeFailed event to find out I was having trouble with the self-referencing constraints. In practice, I doubt self-referencing constraints will ever be an issue for me except during a new client's initial sync with data is converted from an aging, less-than-relational, database and bulk-loaded into an sql server database.
mercredi 16 décembre 2009 20:51

0

Hi Sean,

I also have this problem and am considering disabling the foreign key during synchronization as you suggested above. I was wondering though whether using two scopes might be an option. The first could exclude the child column in the foreign key relationship while the second would include only this column. You would of course need to allow nulls or use a default value in the first scope.

What do you think. Is there any merit to this approach? Is it even possible?

Cheers,

Scot
MCSD.Net
jeudi 17 décembre 2009 19:49

Auteur de réponse

0

The easiest way to handle this is in ApplyChangeFailed and then resolve as RetryNextSync.
Managers will be synced first then employees.