CODEWITHSUNDEEP
sqlsql-serverforeign-keysddlcreate-table
Imraan
Imraan

Reputation: 69

Error when setting foreign key in SQL Server

I have the following queries that I run to 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 the last query I get the error:

There are no primary or candidate keys in the referenced table 'menus_booked' that match the referencing column list in the foreign key 'FK_menu_chan_menu'

I am unsure if my queries are correct and can't resolve this error.

Upvotes: 1

Views: 165

Answers (2)

Mureinik
Mureinik

Reputation: 311508

The primary key of menus_booked is a unique combination of menu_id and booking_id. A foreign must point to that combination, not just one of its fields, which is not necessarily unique. Your query currently tries to define two foreign keys, one on each column, instead of one foreign key on the combination of the 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!
)

Upvotes: 4

HoneyBadger
HoneyBadger

Reputation: 15150

A foreign key has to reference a primary key (or unique key but here the PK is the problem), and it has to reference it in it's entirety. 

FOREIGN KEY (menu_id) REFERENCES menus_booked(menu_id),
FOREIGN KEY (booking_id) REFERENCES menus_booked(booking_id)

You have two foreign key's referencing part of the primary key of menus_booked. You'll have to alter it to:

FOREIGN KEY (menu_id, booking_id) REFERENCES menus_booked(menu_id, booking_id)

Upvotes: 1

Related Questions