Sync Framework 2.1 Foreign key constraints

Question

I have seen many Questions on foreign key constraints problem and what I got is that By default, the following constraints are not copied to the client: FOREIGN KEY constraints, UNIQUE constraints, and DEFAULT constraints

in this document: http://msdn.microsoft.com/en-us/library/bb726037.aspx

So, it appears I have to "manually" create the relationships, once the schema is created on the client.

Once relationship has been created on client side, what if I make any changes in tables on server side, I have to recreate all relationships on client side again and again. Is not it'd be a headache. Is there anyway to write code or script to create foreign key constraints on client side that can be just copied. and if we make any changes on server side tables schema that could be done on client side by changing the script.

Answer 1

I am using a modified version of the sample http://code.msdn.microsoft.com/Database-Sync-SQL-Server-7e88adab#content Sql Server Express to Sql Server over WCF Service.

I used the script from SQL Authority to generate a Alter Table script to add all the foreign keys http://blog.sqlauthority.com/2008/04/18/sql-server-generate-foreign-key-scripts-for-database/

When the client calls the WCF Service GetScopeDescription() to get the Schema for the client I run the above Stored Procedure to get all the Foreign Key relationships to add. The SQL script returned I put in a string in the DbSyncScopeDescription.UserComment field, which holds the script and transports it to the client at the same time as the Schema. Then client side after syncing scope/schema I can run the script generate the relationships.

DbSyncScopeDescription dbSyncScopeDescription = sqlSyncProviderProxy.GetScopeDescription();
sqlSyncScopeProvisioning.PopulateFromScopeDescription(dbSyncScopeDescription);
sqlSyncScopeProvisioning.Apply();
string alterDatabaseScript = dbSyncScopeDescription.UserComment;

This is specific to static database schema/relationships. When schema/relationship modifications are needed I will drop the client database first.

Answer 2

Sync Framework doesnt automatically pick up schema changes made to the tables being synched. whether it's just an FK, column name/type/length change, you will have to re-provision (not unless you want to hack your way on the sync objects).

if you want full schema fidelity, i suggest you create the database objects yourself (table, constraint, sp, triggers, etc...) and not let Sync itself create the tables for you.

and btw, there is no Sync Framework 4.0

Answer 3

I got easy way to add foreign key constrains simply make txt file of foreign key sql commands and give ';' after each sql command and use below code it works perfectly...

private void FunAddForeignKeys()
    {
        SqlConnection clientConn = new SqlConnection(lconString);
                    if (clientConn.State == ConnectionState.Closed)
                        clientConn.Open();
                    System.Data.SqlClient.SqlCommand Command = new System.Data.SqlClient.SqlCommand(GetSql("ForeignKeyQueries.txt"), clientConn);

        try
        {
            Command.ExecuteNonQuery();
            MessageBox.Show("Foreign keys added");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            // Closing the connection should be done in a Finally block
            clientConn.Close();
        }
}
        private string GetSql(string Name)
        {
         try
        {
            // Gets the current assembly.
            Assembly Asm = Assembly.GetExecutingAssembly();

            // Resources are named using a fully qualified name.
            Stream strm = Asm.GetManifestResourceStream(Asm.GetName().Name + "." + Name);

            // Reads the contents of the embedded file.
            StreamReader reader = new StreamReader(strm);
            return reader.ReadToEnd();

        }
        catch (Exception ex)
        {
            MessageBox.Show("In GetSQL: " + ex.Message);
            throw ex;
        }
}

Answer 4

I've got a solution that create client side table via synchronization and then add code to generate foreign key constraints. its easy way rather than to generate all tables ourselves and then add constraints to them. just copy lines for relations and that's all.