Self-referencing Foreign Keys

19 สิงหาคม 2551 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.
- ย้ายโดย Max Wang_Chinasoft 20 เมษายน 2554 0:14 Forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])

ตอบทั้งหมด

20 สิงหาคม 2551 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.
22 สิงหาคม 2551 8:32

ผู้ดูแล

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
22 สิงหาคม 2551 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.
22 สิงหาคม 2551 14:11

ผู้ดูแล

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
22 สิงหาคม 2551 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.
16 ธันวาคม 2552 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
17 ธันวาคม 2552 19:49

ผู้ตอบ

0

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