What is a foreign key?

A FOREIGN KEY is a data integrity constraint in relational databases. It creates a link between two tables by referencing the primary key (or another foreign key) in another table.

Main functions of a foreign key:

• Ensuring referential integrity: Guarantees that values in the foreign key field exist in the referenced parent table's field. This prevents "orphan" records.
• Establishing relationships between tables: Defines how data from different tables are related.
• Cascade actions: Allows automatic execution of actions (deletion or update) in the child table when data in the parent table changes.

Example of creating a foreign key in SQL:

CREATE TABLE `orders` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `customer_name` VARCHAR(255),
    `product_id` INT,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`)
);

CREATE TABLE `products` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `product_name` VARCHAR(255),
    `price` DECIMAL(10, 2)
);

In this example, product_id in the orders table is a foreign key referencing id in the products table. This ensures that each order is linked to an existing product.