locked
Synchronization -> Demo I RRS feed

  • Question

  • Hi!!

    I followed the article of Rafik to make my first synchronization between SQL 2005 and SQLce. Everything works fine, BUT  I've a little problem with primary and foreign keys...

    I've 3 tables :

    create table Person (

    id uniqueidentifier not null,
    firstName nvarchar(25) null,
    lastName nvarchar(25) null,

    constraint PK_PERSON primary key nonclustered (id))

    create table Groups (

    id uniqueidentifier not null,
    name nvarchar(25) null,
    ownerID uniqueidentifier null, -- refers to a Person of table 'Person'
    description ntext null,

    constraint PK_GROUPS primary key nonclustered (id))

    create table TakePart (

    id uniqueidentifier not null,
    personID uniqueidentifier null, -- refers to a Person of table 'Person'
    groupID uniqueidentifier null, -- refers to a Group of table 'Groups'

    constraint PK_TAKEPART primary key nonclustered (id))

    Situation :

    One person take part to one group (= 1 record per table)

    Problem :

    When I want to delete the group with SQL Server Management, it returns an error. And this is completely normal, because this group is still in use in table 'TakePart'!! (error : The DELETE statement conflicted with the REFERENCE constraint "FK_TAKEPART_REFERENCE_GROUPS".)

    BUT

    When I delete this group from the client, it's no problem! The delete operation is made on the client database and when I synchronize it is not deleted on the server database.

    So the client database and server database are no more synchronized! I think this is not very good  

    If somebody can help, thx

    PS: and sorry for my poor english....

    • Moved by Max Wang_1983 Friday, April 22, 2011 11:05 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, March 20, 2007 1:06 PM

Answers

  • Hi,

    The reason you can make the delete on the client is that the FK constraints are not setup between the tables. The automatic creation of the schema on the client does not take care of everything. For example, indexes are not created on the client during schema creation. For this reason, the sync framework fires two schema creation events (CreatingSchema and SchemaCreated events) on the agent interface. These two events allows you to make changes to the schema since you will get access to the connection object and you could use it to setup PK-FK, Check Constraints, Indexes .. etc.

    By the way, on the server, when a change is rejected like in your scenario, another event is fired ApplyChangeFailed event.

    Thanks

    Tuesday, March 20, 2007 4:29 PM

All replies

  • Hi,

    The reason you can make the delete on the client is that the FK constraints are not setup between the tables. The automatic creation of the schema on the client does not take care of everything. For example, indexes are not created on the client during schema creation. For this reason, the sync framework fires two schema creation events (CreatingSchema and SchemaCreated events) on the agent interface. These two events allows you to make changes to the schema since you will get access to the connection object and you could use it to setup PK-FK, Check Constraints, Indexes .. etc.

    By the way, on the server, when a change is rejected like in your scenario, another event is fired ApplyChangeFailed event.

    Thanks

    Tuesday, March 20, 2007 4:29 PM
  • thx Rafik!

     

    I think I will put the structure of database on my client!

    Wednesday, March 28, 2007 8:27 AM