FOREIGN KEY

FOREIGN KEY

A FOREIGN KEY links two tables together. It is a column in one table that points to the primary key of another.

The point

It prevents actions that would break the link between tables. You cannot add an order with a CustomerID that doesn't exist in the Customers table.

Example

CREATE TABLE Orders (
  OrderID int NOT NULL,
  OrderNumber int NOT NULL,
  PersonID int,
  PRIMARY KEY (OrderID),
  FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

• FOREIGN KEY (PersonID) - this table's linking column.
• REFERENCES Persons(PersonID) - points at Persons.PersonID.

Inline shorthand

CREATE TABLE Orders (
  OrderID int PRIMARY KEY,
  PersonID int REFERENCES Persons(PersonID)
);

Add a foreign key later

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Drop a foreign key

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;   -- MySQL (named constraint)

Relationship types

• One-to-many: one customer can have many orders.
• The "many" side (Orders) holds the foreign key.

TL;DR

• FOREIGN KEY links a table to another table's primary key.
• REFERENCES names the target table and column.
• It prevents invalid references.
• The "many" side of a relationship holds the key.