How to reference two tables in Visual Studio 2012 by adding a foreign key?

Question

I have a problem adding a foreign key to a table column. My tables look like this. I need to bind the ContactOwnerId table from Contact to UserId in the 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) ); 

I added a foreign key, but it seems to be wrong, because UserId is allocated, which gives an 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 to refer to these two tables? Thanks in advance.

EDIT I liked sgeddes . But I get an error when I try to create a contact. The INSERT statement was against the FOREIGN KEY constraint "FK_Contacts_UserProfile". The conflict occurred in the database "ContactAppContext", in the table "dbo.UserProfile", in the column "UserId". Application completed.

If I delete the foreign key, I will not get an error.

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

Answer 1

Since ContactOwnerId in the contact table must be FOREIGN KEY , you need to specify this in CONSTRAINT instead of UserId.

I think this is what you are 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]) ); 

The problem was in the last line. First you need to refer to the column from the Contacts table, and then point to the UserProfile table. You had it in the opposite direction.

Here is the SQL script

Here are some documents / examples for creating FOREIGN KEY CONSTRAINTS :

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