如何创建一个DbSyncForeignKeyConstraint到具有复合主键的表 [英] how do I create a DbSyncForeignKeyConstraint to a table with a composite Primary Key

问题描述

我试图创建一个DbSyncForeignKeyConstraint到一个具有复合主键的表，但是我不断收到错误。下面是一些示例代码来演示我在做什么：

示例表：

  GO 
 
 CREATE TABLE [dbo]。[Users]（
 [UserId] [uniqueidentifier] NOT NULL，
 CONSTRAINT [ PK_Users] PRIMARY KEY CLUSTERED 
（
 [UserId] ASC 
）
 WITH 
（
 PAD_INDEX = OFF，
 STATISTICS_NORECOMPUTE = OFF，
 IGNORE_DUP_KEY = OFF，
 ALLOW_ROW_LOCKS = ON，
 ALLOW_PAGE_LOCKS = ON 
）ON [PRIMARY] 
）ON [PRIMARY] 
 
 CREATE TABLE [用户角色]（
 [UserId] [uniqueidentifier]）NOT NULL，
 [RoleId] [uniqueidentifier] NOT NULL，
 CONSTRAINT [PK_UsersRoles] PRIMARY KEY CLUSTERED 
 
 [UserId] ASC，
 [RoleId] ASC 
）
 WITH 
（
 PAD_INDEX = OFF，
 STATISTICS_NORECOMPUTE = OFF，
 IGNORE_DUP_KEY = OFF， 
 ALLOW_ROW_LOCKS = ON，
 ALLOW_PAGE_LOCKS = ON 
）ON [PRIMARY] 
）ON [PRIMARY] 
 
 GO 
  
 
这里是我的C＃代码：
 
 
  DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable（UsersRoles，（SqlConnection）this.dbProvider.Connection）; 
集合< string> parentkeys = new Collection< string>（）; 
 parentkeys.Add（UserId）; 
集合< string> childkeys = new Collection< string>（）; 
 childkeys.Add（RoleId）; 
 childkeys.Add（UserId）; 
 tableDesc.Constraints.Add（new DbSyncForeignKeyConstraint（FK_UsersRoles_Users，Users，UsersRoles，parentkeys，childkeys））; 
  
上面的代码会产生这个错误：
 
 < blockquote> 
 
在引用关系中引用列（例如列数或
类型）的定义必须与引用的列匹配
 
 
如果我用 childkeys.Add（RoleId）注释掉，那么我得到这个错误：
被引用的表必须有主键或候选键。
 
 
 
所以我有点不清楚我该怎么做。在这个例子中，约束应该只是关于UserId列。但是，由于UsersRoles表有一个复合主键，我应该把它包含在约束中吗？如果我在约束定义中省略了RoleId，那么我得到一个错误...如果包含它，我会得到一个不同的错误。
 
 
有没有人关于如何进行的建议？ 
（PS：我有一些代码可以在具有普通的非复合主键的表之间创建其他外键，并且该代码正常工作）。 
 
 
编辑附加信息：
确定。这个问题似乎与DBSyncForeignKeyConstraint的创建没有关系。问题似乎与GetDescriptionForTable功能。它似乎没有拿起作为主键的UserId。在我的项目中，我实际上正在使用aspnet_Membership模式的数据库。所以，如果我脚本的aspnet_Users表，它看起来像这样：
 '
 CREATE TABLE [dbo]。[aspnet_Users]（
 
 [ApplicationId] [ uniqueidentifier] NOT NULL，
 
 [UserId] [uniqueidentifier] NOT NULL，
 
 [UserName] nvarchar NOT NULL，
 
 [LoweredUserName] nvarchar NOT NULL， 
 [MobileAlias] nvarchar NULL，
 
 [IsAnonymous] [bit] NOT NULL，
 
 [LastActivityDate] [datetime] NOT NULL，
 PRIMARY KEY未分类
（
 [UserId] ASC 
）WITH [PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON] ON [PRIMARY] 
） ON [PRIMARY]  
 
 
 GO 
 
 
 
 ALTER TABLE [dbo]。[aspnet_Users] WITH CHECK ADD FOREIGN KEY [ApplicationId]）
参考[dbo]。[aspnet_Applications]（[ApplicationId]）
 GO 
 
 ALTER TABLE [dbo]。[aspnet_Users] ADD DEFAULT（newid（））FOR [UserId] 
 
 GO 
 
 ALTER TABLE [dbo]。[aspnet_Users] AD D DEFAULT（NULL）FOR [MobileAlias] 
 
 GO 
 
 ALTER TABLE [dbo] [aspnet_Users] ADD DEFAULT（（0））FOR [IsAnonymous] GO'解决方案

不知道你是如何供应代码的样子，但是这工作得很好



  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）; 
 $ b $ var parent = SqlSyncDescriptionBuilder.GetDescriptionForTable（Users，sqlConn）; 
 var child = SqlSyncDescriptionBuilder.GetDescriptionForTable（UsersRoles，sqlConn）; 
 
 var parentPKColumns = new集合< string> {用户名}; 
 var childFKColumns = new Collection< string> {用户名}; 
 
 child.Constraints.Add（new DbSyncForeignKeyConstraint（FK_UsersRoles_Users，Users，UsersRoles，parentPKColumns，childFKColumns））; 
 
 var fKTestScope = new DbSyncScopeDescription（FKTestScope）; 
 
 fKTestScope.Tables.Add（parent）; 
 fKTestScope.Tables.Add（child）; 
 
 var scopeProvisioning = new SqlSyncScopeProvisioning（sqlConn2，fKTestScope）; 
 
 scopeProvisioning.Apply（）; 
  




I am trying to create a DbSyncForeignKeyConstraint to a table with a composite Primary Key but, I keep getting errors. Here is some sample code to demonstrate what I am doing:

EXAMPLE TABLES:

USE [TempTest]
GO

CREATE TABLE [dbo].[Users](
        [UserId] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
    (
        [UserId] ASC
    )
    WITH 
    (
        PAD_INDEX  = OFF, 
        STATISTICS_NORECOMPUTE  = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS  = ON, 
        ALLOW_PAGE_LOCKS  = ON
    ) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[UsersRoles](
    [UserId] [uniqueidentifier] NOT NULL,
    [RoleId] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_UsersRoles] PRIMARY KEY CLUSTERED 
    (
        [UserId] ASC,
        [RoleId] ASC
    )
    WITH 
    (
        PAD_INDEX  = OFF, 
        STATISTICS_NORECOMPUTE  = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS  = ON, 
        ALLOW_PAGE_LOCKS  = ON
    ) ON [PRIMARY]
) ON [PRIMARY]

GO

and here is my C# code:

DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("UsersRoles", (SqlConnection)this.dbProvider.Connection);
Collection<string> parentkeys = new Collection<string>();
parentkeys.Add("UserId"); 
Collection<string> childkeys = new Collection<string>();
childkeys.Add("RoleId");
childkeys.Add("UserId");
tableDesc.Constraints.Add(new DbSyncForeignKeyConstraint("FK_UsersRoles_Users", "Users", "UsersRoles", parentkeys, childkeys));

The code above would produce this error:

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

If I comment out the line with childkeys.Add("RoleId"), then I get this error: "The referenced table must have a primary or candidate key."

So I am a little unclear as to how I should do this. The constraint should really only be about the UserId column, in this example. But, since the UsersRoles table has a composite primary key, should I include that in the constraint? If I leave out the "RoleId" in the constraint definition, I get an error...if I include it, I get a different error.

Does anyone have a suggestion about how I should proceed? (PS: I have code that creates other foreign keys between tables with normal non-composite primary keys, and that code works without error).

EDIT WITH ADDITIONAL INFORMATION: OK. The problem doesn't seem to be with the DBSyncForeignKeyConstraint creation. The problem seems to be with the GetDescriptionForTable functionality. It doesn't seem to be picking up the UserId in the as the primary key. In my project, I am actually working against a database which uses the aspnet_Membership schema. So, if I script the aspnet_Users table, it looks like this: ' CREATE TABLE [dbo].[aspnet_Users](
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[UserName] nvarchar NOT NULL,
[LoweredUserName] nvarchar NOT NULL,
[MobileAlias] nvarchar NULL,
[IsAnonymous] [bit] NOT NULL,
[LastActivityDate] [datetime] NOT NULL, PRIMARY KEY NONCLUSTERED ( [UserId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[aspnet_Users] WITH CHECK ADD FOREIGN KEY([ApplicationId]) REFERENCES [dbo].[aspnet_Applications] ([ApplicationId]) GO
ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (newid()) FOR [UserId]
GO
ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (NULL) FOR [MobileAlias]
GO
ALTER TABLE [dbo] [aspnet_Users] ADD DEFAULT ((0)) FOR [IsAnonymous] GO'

But, if I examine the results from calling "GetScopeDescription", I see that the columns "ApplicationId" and "LoweredUserName" are listed as PKColumns and that UserId is listed among the NonPkColumns. I have no idea what is causing this. But, at least I understand why the error is telling me that the column "UserId" is not among the primary keys. It is in the database but, it isn't there in the results of GetScopDescription. So, I know what direction to start searching in, at least.

解决方案

not sure how you're provisioning code looks like, but this works perfectly 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("Users", sqlConn);
        var child = SqlSyncDescriptionBuilder.GetDescriptionForTable("UsersRoles", sqlConn);

        var parentPKColumns = new Collection<string> {"UserId"};
        var childFKColumns = new Collection<string> {"UserId"};

        child.Constraints.Add(new DbSyncForeignKeyConstraint("FK_UsersRoles_Users", "Users", "UsersRoles", parentPKColumns, childFKColumns));

        var fKTestScope= new DbSyncScopeDescription("FKTestScope");

        fKTestScope.Tables.Add(parent);
        fKTestScope.Tables.Add(child);

        var scopeProvisioning = new SqlSyncScopeProvisioning(sqlConn2, fKTestScope);

        scopeProvisioning.Apply();

这篇关于如何创建一个DbSyncForeignKeyConstraint到具有复合主键的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！