如何在Visual Studio 2012中通过添加外键引用两个表？ [英] How to reference two tables in Visual Studio 2012 by adding foreign key?

问题描述

我在为表格列新增外键时发生问题。
我的表看起来这样。我需要引用 ContactOwnerId 从联系表到 UserId UserProfile 表

I have a problem adding a foreign key to a table column. My tables look this way. I need to reference ContactOwnerId from Contact table to UserId in UserProfile table

CREATE TABLE [dbo].[Contacts] (
    [ContactId] INT            IDENTITY (1, 1) NOT NULL,
    [ContactOwnerId]    INT            NOT NULL,
    [FirstName] NVARCHAR (MAX) NOT NULL,
    [LastName]  NVARCHAR (MAX) NOT NULL,
    [Address]   NVARCHAR (MAX) NOT NULL,
    [City]      NVARCHAR (MAX) NOT NULL,
    [Phone]     NVARCHAR (MAX) NOT NULL,
    [Email]     NVARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_dbo.Contacts] PRIMARY KEY CLUSTERED ([ContactId] ASC), 
    CONSTRAINT [FK_Contacts_UserProfile] FOREIGN KEY ([UserId]) REFERENCES [Contacts]([ContactOwnerId])
);

CREATE TABLE [dbo].[UserProfile] (
    [UserId]   INT           IDENTITY (1, 1) NOT NULL,
    [UserName] NVARCHAR (56) NOT NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC),
    UNIQUE NONCLUSTERED ([UserName] ASC)
);

我添加了外键，但似乎不对，因为UserId突出显示，

I added a foreign key, but it seems, not right, because UserId is highlighted, giving error:

SQL71501 :: Foreign Key: [dbo].[FK_Contacts_UserProfile] has an unresolved reference to Column [dbo].[Contacts].[UserId].
SQL71516 :: The referenced table '[dbo].[Contacts]' contains no primary or candidate keys that match the referencing column list in the foreign key. If the referenced column is a computed column, it should be persisted.

如何正确地引用这两个表？提前感谢。

How do I correctly reference these two tables? Thanks in advance.

EDIT
我曾经喜欢 sgeddes 。但我得到一个错误，当我尝试创建一个联系人。
INSERT语句与FOREIGN KEY约束FK_Contacts_UserProfile冲突。冲突发生在数据库ContactAppContext，表dbo.UserProfile，列UserId。
语句已终止。

EDIT I did like sgeddes said. But I get an error, when I try to create a contact. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Contacts_UserProfile". The conflict occurred in database "ContactAppContext", table "dbo.UserProfile", column 'UserId'. The statement has been terminated.

如果我删除外键，我没有错误。

If I remove a foreign key I get no error.

我想实现的是，当用户创建联系人时，他的Id（UserId）可以与ContactOwnerId关联，以便联系人可以与一个特定用户相关。

What I want to achieve is, when a user creates contacts, his Id(UserId) could associate with ContactOwnerId, so that contacts could relate to one specific user.

推荐答案

由于Contact表中的 ContactOwnerId 应该是 FOREIGN KEY 指定在 CONSTRAINT 而不是UserId。

Since ContactOwnerId in the Contact table should be the FOREIGN KEY, you need to specify that in your CONSTRAINT instead of UserId.

我认为这是你想要做的： / p>

I think this is what you're trying to do:

CREATE TABLE [dbo].[UserProfile] (
    [UserId]   INT           IDENTITY (1, 1) NOT NULL,
    [UserName] NVARCHAR (56) NOT NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC),
    UNIQUE NONCLUSTERED ([UserName] ASC)
);

CREATE TABLE [dbo].[Contacts] (
    [ContactId] INT            IDENTITY (1, 1) NOT NULL,
    [ContactOwnerId]    INT            NOT NULL,
    [FirstName] NVARCHAR (MAX) NOT NULL,
    [LastName]  NVARCHAR (MAX) NOT NULL,
    [Address]   NVARCHAR (MAX) NOT NULL,
    [City]      NVARCHAR (MAX) NOT NULL,
    [Phone]     NVARCHAR (MAX) NOT NULL,
    [Email]     NVARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_dbo.Contacts] PRIMARY KEY CLUSTERED ([ContactId] ASC), 
    CONSTRAINT [FK_Contacts_UserProfile] FOREIGN KEY ([ContactOwnerId]) REFERENCES [UserProfile]([UserId])
);

问题出在最后一行。您需要首先从联系人表中引用该列，然后指向您的UserProfile表。你有倒退。

The problem was with the last line. You need to reference the column from the Contacts table first, and then point to your UserProfile table. You had that backwards.

这是一个SQL小提琴

以下是一些创建外部关键字约束的文档/示例：

http://msdn.microsoft.com/ en-us / library / aa258255（v = sql.80）.aspx

这篇关于如何在Visual Studio 2012中通过添加外键引用两个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！