SQL Server : there are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key 'FK'

Question

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.

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'.

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

Solution 1

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

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

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

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.

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

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.

Solution 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.

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

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:

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.