locked
How to provision with FK constraints in the MS Sql server? RRS feed

  • Question

  • Hi, everyboday!

    Happy New Year!

    I am developing the sync app for MS Sql Server 2008 and MS CE. I used the SqlCeSyncProvider as a client provider.

    But I have a problem: when provisioning in SQL server 2008, FK constraints aren't be synced.

    I have tried in several ways, but I got a failed.

    According to MSDN, if I add the tables in the order(Primary Key table and then Foreign Key table), then FK constraints will be set up in client, it said.

    But nothing to sync for FK Constraints.

    Could you help me at this point? Will be appreciate for your any opinions.

    Data sync will be ok except FK constraints.

    Here is my provisioning script.

    if (!serverConfig.ScopeExists(str_ScopeName))

    {

          scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Roles", (System.Data.SqlClient.SqlConnection)provider.Connection));
          scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Users", (System.Data.SqlClient.SqlConnection)provider.Connection))

          serverConfig.PopulateFromScopeDescription(scopeDesc);
                      
          serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
                       
          serverConfig.Apply();

    }

    Thanks for your attention.

    Best Regards

     

    Tuesday, January 4, 2011 12:58 AM

Answers

  • Hi,

    Currently when sync framework get table descriptions (call GetDescriptionForTable(..)), it does not include the foreign key constaint information.

    You can consider manually adding those foreign key constaints by setting this collection at DbSyncTableDescription.Constaints before Apply.

    http://msdn.microsoft.com/en-us/library/dd918945.aspx 

    • Marked as answer by Leonardo Lee Tuesday, January 4, 2011 10:35 PM
    Tuesday, January 4, 2011 7:06 PM
    Answerer
  • Hi,

    I tried it and it is working for me.

    I have a table t1 which is parent and table t2 as child. then

      clientDesc.Tables["dbo.t2"].Constraints.Add(new DbSyncForeignKeyConstraint("fk_t2_t1", "dbo.t1", "dbo.t2", "Id", "Id2"));

    and then apply() created the fk constraint.

    in your case, there are a few problems which need to resolve:

    -  the last statement is unnecessary: scopeDesc.Tables["Roles"].Constraints.Add(fkcons); because you are adding it for users table.

    -  the indexer of Tables should be dbschema.tablename.

    -   Also, please check when you add the table description to scopeDesc, the parent table should be added first (with lower index - like 0), child should be added later (with higher index, like 1).

     

     

    • Marked as answer by Leonardo Lee Wednesday, January 5, 2011 1:07 AM
    Wednesday, January 5, 2011 1:04 AM
    Answerer

All replies

  • Hi,

    Currently when sync framework get table descriptions (call GetDescriptionForTable(..)), it does not include the foreign key constaint information.

    You can consider manually adding those foreign key constaints by setting this collection at DbSyncTableDescription.Constaints before Apply.

    http://msdn.microsoft.com/en-us/library/dd918945.aspx 

    • Marked as answer by Leonardo Lee Tuesday, January 4, 2011 10:35 PM
    Tuesday, January 4, 2011 7:06 PM
    Answerer
  • Hi, Jin

    I tried according to http://msdn.microsoft.com/en-us/library/dd918945.aspx, but not applied.

    Here is my code.

                         string tb_name = "";

                        scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Roles", (System.Data.SqlClient.SqlConnection)provider.Connection));

                        tb_name = "Users";
                        scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(tb_name, (System.Data.SqlClient.SqlConnection)provider.Connection));
     
                        DbSyncForeignKeyConstraint fkcons = new DbSyncForeignKeyConstraint();
                        fkcons.ConstraintName = "FK_Users_Roles";
                        fkcons.ParentTableName = "Roles";
                        fkcons.ParentColumns.Add("RoleId");
                        fkcons.ChildTableName = "Users";
                        fkcons.ChildColumns.Add("RoleId");

                        scopeDesc.Tables[tb_name].Constraints.Add(fkcons);
                        scopeDesc.Tables["Roles"].Constraints.Add(fkcons);

    What is wrong?

    One thing, I want to sync database between Sql 2008 and sql express edition. How can I sync the views on MS Sql?

     

    Thanks for your attention

    Best Regards

     

    Tuesday, January 4, 2011 10:41 PM
  • Hi,

    I tried it and it is working for me.

    I have a table t1 which is parent and table t2 as child. then

      clientDesc.Tables["dbo.t2"].Constraints.Add(new DbSyncForeignKeyConstraint("fk_t2_t1", "dbo.t1", "dbo.t2", "Id", "Id2"));

    and then apply() created the fk constraint.

    in your case, there are a few problems which need to resolve:

    -  the last statement is unnecessary: scopeDesc.Tables["Roles"].Constraints.Add(fkcons); because you are adding it for users table.

    -  the indexer of Tables should be dbschema.tablename.

    -   Also, please check when you add the table description to scopeDesc, the parent table should be added first (with lower index - like 0), child should be added later (with higher index, like 1).

     

     

    • Marked as answer by Leonardo Lee Wednesday, January 5, 2011 1:07 AM
    Wednesday, January 5, 2011 1:04 AM
    Answerer
  • Hi, Jin

    Thanks for your kind help.

    I am very appreciated for your help.

    I will try again.

     

    Well, http://social.microsoft.com/Forums/en-US/syncdevdiscussions/thread/d4a9138a-3663-49ab-a357-63a5f139e3d5

    Would you like to answer this question? This is related with file sync framework.

     

    Best Regards

     

    Wednesday, January 5, 2011 1:10 AM
  •  

    Hi, Jin

    I have tried, but failed.

    My Client Ce provider creates the scope based on the server scope.

    My Sync service creates the scope with the following codes.

     

     

    scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Roles", (System.Data.SqlClient.SqlConnection)provider.Connection));
    tb_name = "dbo.Users";
    DbSyncTableDescription dtd = SqlSyncDescriptionBuilder.GetDescriptionForTable(tb_name, (System.Data.SqlClient.SqlConnection)provider.Connection);
    dtd.Constraints.Add(new DbSyncForeignKeyConstraint("FK_Users_Roles", "dbo.Roles", "dbo.Users", "RoleId", "RoleId"));           
    scopeDesc.Tables.Add(dtd);
    
    //note that it is important to call this after the tables have been added to the scope
    serverConfig.PopulateFromScopeDescription(scopeDesc);
    serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
    serverConfig.Apply();
    
    //Getting again to confirm the FK constraints....
    DbSyncScopeDescription dsd = SqlSyncDescriptionBuilder.GetDescriptionForScope(WandSyncServiceUtils.getScopeName(), (SqlConnection)provider.Connection);
    
    

    At the last statement, I get the scope to confirm the apply result.

    But dsd.Tables[1].Constraints.Count = 0, which means that constraints is not created.

    I don't know what the problem is really.

    Please give me your opinion. Would you like to share your code which works well?

     

    Best Regards

     

    Thursday, January 6, 2011 7:42 PM
  • Hi, Jin.

    I added the FK constraints manually. But It doesn't work.

    Because I used the DbSyncCreationOption.Skip and server db has lots of FK constraints already, so scope definition of FK constraints doesn't applied to client and server.

    How can I overcome this problem. Please note that there is already db schema with FK constraints on the server Db.

     

    Best Regards

    Thursday, January 20, 2011 4:12 AM