Geek Answers Handbook

SQL Server: in the reference table there are no primary or potential keys that match the list of link columns in the foreign key "FK",

I tried to solve this error in a few minutes, but I do not know what I am missing from the table definition.

Below is the code for the tables:

Autocare table:

 CREATE TABLE [dbo].[Autocare] ( [IDAutocar] NUMERIC (18, 0) NOT NULL, [IDTipAutocar] NUMERIC (18, 0) NOT NULL, PRIMARY KEY CLUSTERED ([IDAutocar] ASC, [IDTipAutocar] ASC), CONSTRAINT [FK_Autogari_TipAutocar] FOREIGN KEY ([IDTipAutocar]) REFERENCES [dbo].[TipAutocar] ([IDTipAutocar]) );

Curse table:

 CREATE TABLE [dbo].[Curse] ( [IDCursa] NUMERIC (18, 0) NOT NULL PRIMARY KEY, [IDTraseu] NUMERIC (18, 0) NOT NULL, [Data] TIMESTAMP NOT NULL, [IDCompanie] NUMERIC (18, 0) NOT NULL, [NrLocuri] NUMERIC (18, 0) NOT NULL, [IDAutocar] NUMERIC (18, 0) NOT NULL, CONSTRAINT [FK_Curse_Trasee] FOREIGN KEY ([IDTraseu]) REFERENCES [Trasee]([IDTraseu]), CONSTRAINT [FK_Curse_Companii] FOREIGN KEY ([IDCompanie]) REFERENCES [Companii]([IDCompanie]), CONSTRAINT [FK_Curse_Autocare] FOREIGN KEY ([IDAutocar]) REFERENCES [Autocare]([IDAutocar]) )

When I try to execute the second script, I get the following error (and I know something is related to the relationship between the tables), and I do not understand where the problem may be. I am new to C # and SQL Server, so please forgive me if I asked a stupid question.

Msg 1776, Level 16, State 0, Line 1
The Autocare reference table does not have primary or potential keys that match the list of link columns in the foreign key FK_Curse_Autocare.

Msg 1750, Level 16, State 0, Line 1
Failed to create constraint. See Previous Errors.

+5
source share
2 answers

Your Autocare table has a composite primary key that consists of two columns :

 PRIMARY KEY CLUSTERED ([IDAutocar] ASC, [IDTipAutocar] ASC),

Therefore, any table that wants to reference Autocare must also provide both columns in their foreign key!

So this will obviously not :

 CONSTRAINT [FK_Curse_Autocare] FOREIGN KEY ([IDAutocar]) REFERENCES [Autocare]([IDAutocar])

since it only refers to one of the two columns of the Autocare primary key.

You need to add this second IDTipAutocar column to the Curse table and include it in your foreign key:

 CONSTRAINT [FK_Curse_Autocare] FOREIGN KEY ([IDAutocar], [IDTipAutocar]) REFERENCES [Autocare]([IDAutocar], [IDTipAutocar])

Your foreign keys should always refer to the primary (WHOLE ) primary key, not just parts.

+7
source

A foreign key must refer to a unique key, whether primary or not. In your current supplied DDL, Autocare unique identifier (primary key) is a combination of IDAutocar and IDTipAutocar . On the other hand, you have a Curse link Autocare.IDAutocar , which is not unique.

You can add IDTipAutocar to Curse (and the definition of its foreign key):

 CREATE TABLE [dbo].[Curse] ( [IDCursa] NUMERIC (18, 0) NOT NULL PRIMARY KEY, [IDTraseu] NUMERIC (18, 0) NOT NULL, [Data] TIMESTAMP NOT NULL, [IDCompanie] NUMERIC (18, 0) NOT NULL, [NrLocuri] NUMERIC (18, 0) NOT NULL, [IDAutocar] NUMERIC (18, 0) NOT NULL, [IDTipAutocar] NUMERIC (18, 0) NOT NULL, CONSTRAINT [FK_Curse_Trasee] FOREIGN KEY ([IDTraseu]) REFERENCES [Trasee]([IDTraseu]), CONSTRAINT [FK_Curse_Companii] FOREIGN KEY ([IDCompanie]) REFERENCES [Companii]([IDCompanie]), CONSTRAINT [FK_Curse_Autocare] FOREIGN KEY ([IDAutocar], [IDTipAutocar]) REFERENCES [Autocare]([IDAutocar], [IDTipAutocar]) )

Alternatively, you can remove the IDTipAutocar from the Autocare primary key definition:

 CREATE TABLE [dbo].[Autocare] ( [IDAutocar] NUMERIC (18, 0) NOT NULL, [IDTipAutocar] NUMERIC (18, 0) NOT NULL, PRIMARY KEY CLUSTERED ([IDAutocar] ASC), CONSTRAINT [FK_Autogari_TipAutocar] FOREIGN KEY ([IDTipAutocar]) REFERENCES [dbo].[TipAutocar] ([IDTipAutocar]) );

What ever makes more sense in terms of your business logic.

+1
source

More articles:


All Articles