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

Popular posts from this blog

javascript - Laravel datatable invalid JSON response -

java - Exception in thread "main" org.springframework.context.ApplicationContextException: Unable to start embedded container; -

sql server 2008 - My Sql Code Get An Error Of Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '8:45 AM' to data type int -