none
Can't create a Foreign Key constraint to a table with a composite primary key. RRS feed

  • Question

  • In my C# sync code, I am attempting to create a foreign key constraint to a table that has a composite primary key. But, the constraint only needs to be to one of the two columns that make up the composite key. Is this possible?

    As an example, consider the aspnet_Roles and aspnet_UsersInRoles tables from the aspnet membership database schema. The aspnet_UsersInRoles table has two columns, UserId and RoleId. If you wanted to create a foreign key constraint between the aspnet_UsersInRoles table and the aspnet_Roles table, would you do it like this:

    tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("aspnet_UsersInRoles", (SqlConnection)this.dbProvider.Connection);
    tableDesc.Constraints.Add(new DbSyncForeignKeyConstraint("FK_aspnet_UsersInRoles_aspnet_Roles", "aspnet_Roles", "aspnet_UsersInRoles", "RoleId", "RoleId"));
    scopeDesc.Tables.Add(tableDesc);
    

    If I do this, I get an error:

    [System.Data.SqlServerCe.SqlCeException] = {"The referenced table must have a primary or candidate key. [ FK Name = FK_aspnet_UsersInRoles_aspnet_Roles ]"}

    If I try to add multiple columns in, using this type of syntax:

    tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("aspnet_UsersInRoles", (SqlConnection)this.dbProvider.Connection);
    Collection<string> col1 = new Collection<string>();
    col1.Add("RoleId");
    Collection<string> col2 = new Collection<string>();
    col2.Add("RoleId");
    col2.Add("UserId");
    tableDesc.Constraints.Add(new DbSyncForeignKeyConstraint("FK_aspnet_UsersInRoles_aspnet_Roles", "aspnet_Roles", "aspnet_UsersInRoles", col1, col2));

    I get this error:

    The definition of referring columns (such as number of columns or data types) in referential relationships must match the referred columns.

    So, I am stumped. Is it not possible to create a foreign key constraint to a table that has a composite primary key?

    Thank you, in advance, for whatever help you can provide.

    Friday, September 21, 2012 12:23 PM

All replies

  • not sure about your provisioning code, but this test works out fine:

                var sqlConn = new SqlConnection(@"Data Source=(local)\SQLExpress; Initial Catalog=Test; Integrated Security=True");
                var sqlConn2 = new SqlConnection(@"Data Source=(local)\SQLExpress; Initial Catalog=Test2; Integrated Security=True");
    
                var parent = SqlSyncDescriptionBuilder.GetDescriptionForTable("aspnet_Roles", sqlConn);
                var child = SqlSyncDescriptionBuilder.GetDescriptionForTable("aspnet_UsersInRoles", sqlConn);
    
                var parentPKColumns = new Collection<string> {"RoleId"};
                var childFKColumns = new Collection<string> {"RoleId"};
    
                child.Constraints.Add(new DbSyncForeignKeyConstraint("FK_Test", "aspnet_Roles", "aspnet_UsersInRoles", parentPKColumns, childFKColumns));
                
                var fKTestScope= new DbSyncScopeDescription("FKTestScope");
                
                fKTestScope.Tables.Add(parent);
                fKTestScope.Tables.Add(child);
    
                var scopeProvisioning = new SqlSyncScopeProvisioning(sqlConn2, fKTestScope);
    
                scopeProvisioning.Apply();

     
    Saturday, September 22, 2012 2:53 AM
    Moderator