Database.What is a “foreign key”?

A foreign key is a column (or set of columns) in one table that refers to the primary key in another table. It is used to establish a relationship between two tables and enforce referential integrity.

🔁 Purpose of a Foreign Key:

• To link related data across tables.
• To prevent invalid data from being inserted (e.g., you can’t reference a customer that doesn’t exist).
• To enable joins in queries.

📊 Example:

Table: Customers

customer_id (PK)	name
1	Alice
2	Bob

Table: Orders

order_id (PK)	customer_id (FK)	product
101	1	Laptop
102	2	Phone
103	3	Tablet ❌ (invalid if customer 3 doesn’t exist)

In this case:

• customer_id in Orders is a foreign key that references customer_id in Customers.

---

🔐 Foreign Key Constraints:

1. ON DELETE CASCADE – Delete related rows automatically.
2. ON DELETE SET NULL – Set foreign key to NULL when the parent is deleted.
3. ON DELETE RESTRICT / NO ACTION – Prevent deletion if references exist.

---

🛠️ SQL Example:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product VARCHAR(100),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

✅ Key Properties:

Property	Description
Must match	Value must exist in the parent table
Can be null	Often allowed unless specified as NOT NULL
Enforces links	Maintains data integrity between tables