Lesson 8: Keys Part 2: Foreign Keys (FK) and Relationships

While Primary Keys define the records within one table, Foreign Keys are what link two tables together, defining the relationships.

What is a Foreign Key (FK)?

A Foreign Key (FK) is a column (or set of columns) in one table that references the Primary Key of another table.

The Foreign Key is the bridge that connects the data.

Role of the Foreign Key (Referential Integrity)

The FK enforces Referential Integrity, which means the relationship between the tables must be valid. You cannot create a record in the 'child' table that references a non-existent record in the 'parent' table.

Example: Orders and Customers

Parent Table: Customers (PK is CustomerID)
Child Table: Orders (Needs to know which customer placed the order)

We introduce a CustomerID column into the Orders table. This new column is the Foreign Key.

sql -- Table 1: Parent CREATE TABLE Customers ( CustomerID INTEGER PRIMARY KEY, Name VARCHAR(100) );

-- Table 2: Child CREATE TABLE Orders ( OrderID INTEGER PRIMARY KEY, OrderDate DATE, CustomerID INTEGER, -- This is the Foreign Key

-- Defining the FK constraint
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

);

This constraint ensures:

Every OrderID must belong to a CustomerID that actually exists in the Customers table.
You cannot delete a customer if there are still orders associated with them (unless specified otherwise).