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

sql

### 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

sql

### CREATE TABLE Orders (

### OrderID int PRIMARY KEY,

### PersonID int REFERENCES Persons(PersonID)
);

Add a foreign key later

sql

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

Drop a foreign key

sql

### 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.