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,
PRIMARY KEY CLUSTERED ([StaffID] ASC)
);
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.