Share
Go down
avatar
Admin
Posts : 155
Join date : 2017-11-11
Age : 27
View user profilehttp://net-media.yoo7.com

I cannot set foreign keys in my table

on Wed Dec 13, 2017 2:41 pm
It shows
There are no primary or candidate keys in the referenced table 'Dates' that match the referencing column list in the foreign key 'FK__SpecialAc__Itine__367C1819'. ".
Code:
create table Itinerary(
    ItineraryId int primary key identity,
    Description varchar(1000),
    TourId varchar(5)
    FOREIGN KEY (TourId) REFERENCES Tour(TourId) 
);

create table DatesInfo(
    Dates date NOT NULL,
    ItineraryId int NOT NULL,
    PlaceOfBreakfirst varchar(100) NOT NULL,
    PlaceOfLunch varchar(100) NOT NULL,
    PlaceOfDinner varchar(100) NOT NULL,
    PlaceOfAccommodation varchar(100) NOT NULL,
    Route varchar(1000) NOT NULL,
    ScheduleId int NOT NULL,
    PRIMARY KEY(Dates,ItineraryId),
    FOREIGN KEY (ScheduleId) REFERENCES Schedule(ScheduleId) 
);

create table SpecialActivity(
    Activity varchar(100),
    ItineraryId int NOT NULL,
    Dates date NOT NULL,
    PRIMARY KEY(Activity,ItineraryId,Dates),   
    FOREIGN KEY (ItineraryId) REFERENCES DatesInfo(ItineraryId),
    FOREIGN KEY (Dates) REFERENCES DatesInfo(Dates)       
);

Answer
You're trying to create two separate foreign keys:
Code:
FOREIGN KEY (ItineraryId) REFERENCES DatesInfo(ItineraryId),
FOREIGN KEY (Dates) REFERENCES DatesInfo(Dates)
But the referenced table has one (composite) primary key:
Code:
PRIMARY KEY(Dates,ItineraryId),
The foreign key needs to match the primary key in the target table. So you need a composite foreign key:
Code:
FOREIGN KEY (Dates,ItineraryId) REFERENCES DatesInfo(Dates,ItineraryId),

Back to top
Permissions in this forum:
You can reply to topics in this forum