Click here to Skip to main content
15,445,242 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am trying to create a bridge table (StaffRota) between the BookedDates and Staff table by making DateBooked a foreign key in the StaffRota table, but I am getting the error:
SQL71516 :: The referenced table '[dbo].[BookedDates]' 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.

Here are my tables:

CREATE TABLE [dbo].[StaffRota] (
    [DateBooked]    DATE       NOT NULL,
    [StaffID]       INT        NOT NULL,
    [Working]       NCHAR (10) NULL,
    PRIMARY KEY CLUSTERED ([DateBooked], [StaffID]),
    CONSTRAINT [FK_Staff_To_StaffRota] FOREIGN KEY ([StaffID]) REFERENCES [dbo].[Staff] ([StaffID]),
	CONSTRAINT [FK_Staff_To_BookedDates] FOREIGN KEY ([DateBooked]) REFERENCES [dbo].[BookedDates] ([DateBooked])

CREATE TABLE [dbo].[Staff] (
    [StaffID]   INT        IDENTITY (0, 1) NOT NULL,
    [Name]      NCHAR (20) NULL,
    [ShiftType] NCHAR(20)  NULL, 

CREATE TABLE [dbo].[BookedDates] (
    [BookingRef]     INT         NOT NULL,
    [DateBooked]     DATE        NOT NULL,
    [CollectionTime] NCHAR (10)  NULL,
    [DropOffTime]    NCHAR (10)  NULL,
    [Late]           NCHAR (10)  NULL,
    [KennelNo]       INT         NULL,
    [Walked]         NCHAR (10)  NULL,
    [Fed]            NCHAR (10)  NULL,
    [Note]           NCHAR (100) NULL,
    [Trial]          NCHAR (10)  NULL,
    PRIMARY KEY CLUSTERED ([BookingRef] ASC, [DateBooked] ASC),
    CONSTRAINT [FK_BookedDates_To_Booking] FOREIGN KEY ([BookingRef]) REFERENCES [dbo].[Booking] ([BookingRef])

What I have tried:

Have tried deleting the table and tried to create the FK again but no success.
Updated 1-Apr-21 8:56am

1 solution

Try creating your tables in a different order: Staff first, then StaffRota, then BookedDates

That way, when you try to create a Foreign Key, the target table and column should already exist.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900