0

I have three tables a DaysOfWeek, an OpeningHours and a Shops:

DaysOfWeek

Id tinyint PK
Day nvarchar(10)

OpeningHours 

Id int PK
DaysOfWeekId tinyint PK
OpeningHour varchar(16)

Shops

Id int
...
OpeningHoursId int PK
...

ALTER TABLE Shops
ADD CONSTRAINT FK_Shops_OpeningHours
FOREIGN KEY (OpeningHoursId) REFERENCES OpeningHours(Id);

I cannot link the Shops and the OpeningHours tables due to this error:

There are no primary or candidate keys in the referenced table 'OpeningHours' that match the referencing column list in the foreign key 'FK_Shops_OpeningHours'.

Maybe it is not possible because of the OpeningHours' composite key?

Filburt
  • 17,626
  • 12
  • 64
  • 115
Sándor Hatvani
  • 435
  • 1
  • 7
  • 21
  • 2
    Why *does* `OpeningHours` have a composite key? I would have suggested that `Id` is the PK`, not `Id, and `DaysOfWeekId`. Is `Id` not a unique value on its own in `OpeningHours`? If you want to make a foreign key relationship to `OpeningHours` it'll need to be on `ID` and `DaysOfWeekId`. – Thom A Nov 28 '18 at 09:18
  • Possible duplicate of [Foreign key relationship with composite primary keys in SQL Server 2005](https://stackoverflow.com/questions/3996774/foreign-key-relationship-with-composite-primary-keys-in-sql-server-2005) – Dale K Nov 28 '18 at 09:19
  • A foreign key references the primary key of another table. That is, the *entire* primary key. And similar to Larnu's comment, why does `shops` have `OpeningHoursId` as part of its PK? – HoneyBadger Nov 28 '18 at 09:20
  • Due to the specification but I will create another table with the composite key of OpeningHours and a plus PH which will be the FK in Shops table. ? – Sándor Hatvani Nov 28 '18 at 09:52
  • Thank you Dale Burrell I see it. – Sándor Hatvani Nov 28 '18 at 10:03

0 Answers0