What are foreign keys in a database?

A Foreign Key is a column or a set of columns in a child table that references the primary key of a parent table.

Main functions and concepts:

• Table linking: Foreign keys establish a relationship between two tables, acting as a link. For example, in the orders table, there might be a foreign key customer_id referencing the primary key id in the customers table.
• Data integrity: Ensures referential integrity, guaranteeing that data in the child table correctly references existing data in the parent table. You cannot insert a record into the child table with a foreign key that does not match an existing primary key in the parent table.
• Interaction on changes: Behavior on deletion or update of records in the parent table can be configured using rules like CASCADE, RESTRICT, SET NULL, SET DEFAULT, or NO ACTION.

Example of creating a table with a foreign key in SQL:

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(255)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE CASCADE
);

Here, category_id in the products table is a foreign key referencing category_id (the primary key) in the categories table. ON DELETE CASCADE means that when a category is deleted, all related products will also be deleted.