What is a foreign key?

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

🔗 Purpose of a Foreign Key:

• To connect tables logically (e.g., orders linked to customers).
• To ensure consistency, meaning only valid data that exists in the referenced table can be inserted.

Example:

Let’s say we have two tables:

-- Parent table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Child table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Here:

• customers.customer_id is the primary key.
• orders.customer_id is the foreign key that refers to customers.customer_id.

So, you can’t insert an order with a customer_id that doesn’t exist in the customers table.

🔒 Key Rules of Foreign Keys:

🔄 Example with ON DELETE CASCADE:

FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE

This means: if a customer is deleted, all their related orders are also deleted automatically.

Summary:

Want an ER diagram or visual to go along with this? I can mock one up for you!