CODEWITHSUNDEEP
sqlsql-serverdatabase-design
neda Derakhshesh
neda Derakhshesh

Reputation: 1163

Error contains no primary or candidate keys that match the referencing column

This is my first Table : Tours

CREATE TABLE [dbo].[Tours] (

[Id]              INT            IDENTITY (1, 1) NOT NULL,
[TourId]          INT            NOT NULL,
[TCountry]        NVARCHAR (50)  NULL,
[TName]           NVARCHAR (100) NULL,


CONSTRAINT [PK_Tours] PRIMARY KEY CLUSTERED ([TourId]),
CONSTRAINT [FK_Tours_ToTourDates] FOREIGN KEY ([TourId]) REFERENCES [TourDates]([TourId]));

and this is the next, Table:TourDates

CREATE TABLE [dbo].[TourDates] (

[Id]            INT            IDENTITY (1, 1) NOT NULL,
[TourId]        INT            NOT NULL,
[TourStartDate] DATETIME       NULL,


CONSTRAINT [PK_TourDates] PRIMARY KEY CLUSTERED ([Id] ASC));

for first table I have this Error :

SQL71516 :: The referenced table '[dbo].[TourDates]' 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 can one define a Candidate key for TourId column in TourDates Table??

Upvotes: 0

Views: 7747

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17137

I believe that you should be doing something else than you are doing right now. Tours table seems to hold every tour which should be in 1:N relationship with TourDates provided that I understand it correctly.

Thus, your FOREIGN KEY constraint should actually be declared on TourDates, not Tours table.

As to your issue (which I believe wouldn't if you switch those relationships to what they should be in my understanding) in such cases you would normally need to create a unique index on that column.

You can't reference a column which may contain several exact same values via foreign key constraint, thus the need for some sort of a unique key.

Edit after comment:

ALTER TABLE [dbo].[TourDates]
   ADD CONSTRAINT FK_Tour_TourDates
   FOREIGN KEY ([TourId]) REFERENCES [dbo].[Tours]([TourId]) ON DELETE CASCADE

Upvotes: 2

Related Questions