Error when setting foreign key in SQL Server -
i have following queries run create tables in ms sql server:
create table menus ( menu_id int not null primary key, menu_name char, other_details char ) create table bookings ( booking_id int not null primary key, date_booked date, date_of_booking date, other_details char, staff_id int foreign key references staff(staff_id), customer_id int foreign key references customers(customer_id) ) create table menus_booked ( menu_id int not null, booking_id int not null, constraint pk_menus_booked primary key(menu_id,booking_id), foreign key (menu_id) references menus(menu_id), foreign key (booking_id) references bookings(booking_id) ) create table menu_changes ( change_id int not null primary key, menu_id int not null, booking_id int not null, change_details char, foreign key (menu_id) references menus_booked(menu_id), foreign key (booking_id) references menus_booked(booking_id) )
on running last query error:
there no primary or candidate keys in referenced table 'menus_booked' match referencing column list in foreign key 'fk_menu_chan_menu'
i unsure if queries correct , can't resolve error.
the primary key of menus_booked
unique combination of menu_id
, booking_id
. foreign must point combination, not 1 of fields, not unique. query tries define 2 foreign keys, 1 on each column, instead of 1 foreign key on combination of columns:
create table menu_changes ( change_id int not null primary key, menu_id int not null, booking_id int not null, change_details char, foreign key (menu_id, booking_id) references menus_booked(menu_id, booking_id) -- here! )
Comments
Post a Comment