Database.Beginner.What is a foreign key?

A foreign key is a column (or a set of columns) in a table that creates a link between data in two tables. It acts as a reference to a primary key in another table.

Key points about foreign keys:

• Referential Integrity: The foreign key ensures that the value in the column must match a value in the referenced table’s primary key (or a unique key).
• Relationships: It’s used to define relationships between tables (e.g., one-to-many, many-to-one).
• Validation: You cannot insert a value into a foreign key column if that value doesn’t exist in the referenced table.
• Cascading: You can define actions like ON DELETE CASCADE or ON UPDATE CASCADE to automatically delete or update dependent rows.

Example:

Suppose you have two tables, Users and Orders.

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

Users.user_id is the primary key.

Orders.user_id is a foreign key that references Users.user_id.

This means every user_id in Orders must exist in the Users table.

Visual:

Users	Orders
user_id	username
1	Alice
2	Bob

If you try to insert an order with user_id = 3, but user_id 3 does not exist in Users, the database will reject the insert.