SQL Server：引用的表中没有与外键“FK”中的引用列列表匹配的主键或候选键

I've been trying to solve this error for several minutes but I don't know what am I missing from table definition.

我一直试图解决这个错误几分钟,但我不知道我在表定义中缺少什么。

The code for the tables are below:

表格的代码如下:

Table Autocare:

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])
);

Table Curse:

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 is something related to the relationship between tables) and I don't figure out where the problem might be. I'm a newbie into C# and SQL Server, so please forgive if I asked a stupid question.

当我尝试执行第二个脚本时,我得到以下错误(我知道是与表之间的关系有关的东西),我不知道问题可能在哪里。我是C#和SQL Server的新手,所以如果我问了一个愚蠢的问题,请原谅。

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Autocare' that match the referencing column list in the foreign key 'FK_Curse_Autocare'.

消息1776,级别16,状态0,行1在引用的表“Autocare”中没有与外键“FK_Curse_Autocare”中的引用列列表匹配的主键或候选键。

Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

消息1750,级别16,状态0,行1无法创建约束。查看以前的错误。

2 个解决方案

#1

Your table Autocare has a compound primary key made up from two columns:

您的表Autocare具有由两列组成的复合主键:

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

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

因此,任何希望引用Autocare的表都必须在其外键中提供两列!

So this will obviously not work:

所以这显然不起作用:

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

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

因为它只引用Autocare主键的两列中的一列。

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

您需要将第二列IDTipAutocar添加到您的Curse表并将其包含在您的外键中:

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

Your foreign keys must always reference the WHOLE primary key - not just parts of it.

您的外键必须始终引用WHOLE主键 - 而不仅仅是它的一部分。

#2

A foreign key must reference a unique key, be it primary or not. In your current supplied DDL, the unique identifier (primary key) in Autocare is the combination of IDAutocar and IDTipAutocar. On the other hand, you have Curse referencing Autocare.IDAutocar, which is not unique.

外键必须引用唯一键,无论是否为主键。在当前提供的DDL中,Autocare中的唯一标识符(主键)是IDAutocar和IDTipAutocar的组合。另一方面,你有Curse引用Autocare.IDAutocar,它不是唯一的。

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

您可以将IDTipAutocar添加到Curse(及其外键定义):

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 could remove IDTipAutocar from Autocare's primary key definition:

或者,您可以从Autocare的主键定义中删除IDTipAutocar:

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])
);

Which ever makes more sense from your business logic's perspective.

从业务逻辑的角度来看,这更有意义。

#1