CODEWITHSUNDEEP
sqlvisual-studio-2015
AndyHyl15
AndyHyl15

Reputation: 5

Azure SQL Foreign Key with Visual Studio 2015

I have an Azure SQL database created in the management station, I connect to it on Visual Studio 2015, I create tables Player and Team, I'm trying to create a foreign key to reference team name but I keep getting the following error;

SQL71516 :: The referenced table '[dbo].[Team]' 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.

From looking at online sources, mainly MSDN, I have tried a few ways to solve this issue but have had no luck. Here is my SQL code;

Team Table:

CREATE TABLE [dbo].[Team] (
    [Id]             INT          NOT NULL,
    [Name]           VARCHAR (30) NOT NULL,
    [Wins]           INT          NOT NULL,
    [Draws]          INT          NOT NULL,
    [Losses]         INT          NOT NULL,
    [GoalsFor]       INT          NOT NULL,
    [GoalsAgainst]   INT          NOT NULL,
    [GoalDifference] INT          NOT NULL,
    [Points]         INT          NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Player Table:

CREATE TABLE [dbo].[Player] (
    [Id]      INT          NOT NULL,
    [Name]    VARCHAR (30) NOT NULL,
    [Team]    VARCHAR (30) NOT NULL,
    [Goals]   INT          NOT NULL,
    [Assists] INT          NOT NULL,
    [Apps]    INT          NOT NULL,
    [Club] VARCHAR (30)    NOT NULL,
    CONSTRAINT [FK_Player_Club] FOREIGN KEY ([Club]) REFERENCES [dbo].[Team]([Name]),
   PRIMARY KEY CLUSTERED ([Id] ASC)
);

Upvotes: 0

Views: 572

Answers (1)

jarlh
jarlh

Reputation: 44766

Team Table:

CREATE TABLE [dbo].[Team] (
    [Id]             INT          NOT NULL,
    [Name]           VARCHAR (30) NOT NULL,
    [Wins]           INT          NOT NULL,
    [Draws]          INT          NOT NULL,
    [Losses]         INT          NOT NULL,
    [GoalsFor]       INT          NOT NULL,
    [GoalsAgainst]   INT          NOT NULL,
    [GoalDifference] INT          NOT NULL,
    [Points]         INT          NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Player Table:

CREATE TABLE [dbo].[Player] (
    [Id]      INT          NOT NULL,
    [Name]    VARCHAR (30) NOT NULL,
    [Team]    VARCHAR (30) NOT NULL,
    [Goals]   INT          NOT NULL,
    [Assists] INT          NOT NULL,
    [Apps]    INT          NOT NULL,
    [TeamId]  INT          NOT NULL,
    CONSTRAINT [FK_Player_Club] FOREIGN KEY ([TeamId]) REFERENCES [dbo].[Team]([Id]),
   PRIMARY KEY CLUSTERED ([Id] ASC)
);

Upvotes: 0

Related Questions