What is the idea behind foreign keys?

A Foreign Key is a column (or set of columns) in one table that references the primary key (or a unique key) in another table. It establishes a link between two tables, ensuring referential integrity of the data.

The idea is as follows:

1. Linking tables: Allows linking rows from one table to corresponding rows in another table based on a common value.
2. Maintaining integrity: Ensures that a reference in the foreign table points to an existing row in the parent table (the table being referenced by the foreign key). For example, you cannot delete a user who is referenced by order records without first deleting or updating those orders.
3. Operation constraints: Defines what happens to data in the secondary table when data in the parent table is modified or deleted. The following actions (constraints) are possible:
   • ON DELETE CASCADE: Automatically deletes dependent rows in the secondary table.
   • ON UPDATE CASCADE: Automatically updates dependent rows in the secondary table.
   • ON DELETE SET NULL: Sets the foreign key value to NULL when the parent row is deleted (if the column allows NULL).
   • ON UPDATE SET NULL: Sets the foreign key value to NULL when the parent row is updated.
   • ON DELETE RESTRICT: Prevents deletion of the parent row if dependent rows reference it.
   • ON UPDATE RESTRICT: Prevents updating the parent row.
   • NO ACTION (default in some DBMS): Similar to RESTRICT, but the check is deferred until the end of the transaction.
4. Modeling relationships: Allows modeling various types of relationships between entities in a database, such as "one-to-many" or "many-to-many" (via a junction table).

Example of creating a foreign key in SQL:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255)
);