Databases
StackOverflow2013
Postsdbo
POhow do I create a DbSyncForeignKeyConstraint to a table with a composite Primary Key
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow do I create a DbSyncForeignKeyConstraint to a table with a composite Primary Key
    text
    copied!<p>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:</p> <p>EXAMPLE TABLES:</p> <pre><code>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 </code></pre> <p>and here is my C# code:</p> <pre><code>DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("UsersRoles", (SqlConnection)this.dbProvider.Connection); Collection&lt;string&gt; parentkeys = new Collection&lt;string&gt;(); parentkeys.Add("UserId"); Collection&lt;string&gt; childkeys = new Collection&lt;string&gt;(); childkeys.Add("RoleId"); childkeys.Add("UserId"); tableDesc.Constraints.Add(new DbSyncForeignKeyConstraint("FK_UsersRoles_Users", "Users", "UsersRoles", parentkeys, childkeys)); </code></pre> <p>The code above would produce this error:</p> <blockquote> <p>The definition of referring columns (such as number of columns or data types) in referential relationships must match the referred columns.</p> </blockquote> <p>If I comment out the line with <code>childkeys.Add("RoleId")</code>, then I get this error: "The referenced table must have a primary or candidate key."</p> <p>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.</p> <p>Does anyone have a suggestion about how I should proceed? (PS: I have code that creates other foreign keys between tables with normal <em>non-composite</em> primary keys, and that code works without error).</p> <p>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: <code>' CREATE TABLE [dbo].[aspnet_Users](<br> [ApplicationId] [uniqueidentifier] NOT NULL,<br> [UserId] [uniqueidentifier] NOT NULL,<br> [UserName] nvarchar NOT NULL,<br> [LoweredUserName] nvarchar NOT NULL,<br> [MobileAlias] nvarchar NULL,<br> [IsAnonymous] [bit] NOT NULL,<br> [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]</p> <p>GO</p> <p>ALTER TABLE [dbo].[aspnet_Users] WITH CHECK ADD FOREIGN KEY([ApplicationId]) REFERENCES [dbo].[aspnet_Applications] ([ApplicationId]) GO<br> ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (newid()) FOR [UserId]<br> GO<br> ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (NULL) FOR [MobileAlias]<br> GO<br> ALTER TABLE [dbo] [aspnet_Users] ADD DEFAULT ((0)) FOR [IsAnonymous] GO'</code></p> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload