SQL FOREIGN KEY Constraint

 In SQL, a FOREIGN KEY constraint is used to enforce a link between the columns in two tables. It ensures that the value in the child table's foreign key column matches a value in the parent table's primary key or unique key column. This helps maintain data integrity by ensuring that relationships between tables remain consistent.

Syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES parent_table(parent_column)
);

Example:

Let's say we have two tables: orders and customers.

• customers table has a customer_id (which is the primary key).
• orders table has a customer_id (which is a foreign key linking to customers).

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

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

Key points:

• Parent table: The table that contains the primary key (e.g., customers).
• Child table: The table that contains the foreign key (e.g., orders).
• Referential Integrity: The foreign key ensures that values in the child table must exist in the parent table (the customer_id in orders must match a customer_id in customers).

Additional options:

• ON DELETE CASCADE: If a record in the parent table is deleted, the corresponding records in the child table are also deleted.
• ON UPDATE CASCADE: If a record in the parent table is updated, the corresponding records in the child table are also updated.
• ON DELETE SET NULL: If a record in the parent table is deleted, the corresponding foreign key in the child table is set to NULL.

Example with options:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

This ensures that if a customer is deleted, all their associated orders will also be deleted. Similarly, if the customer_id is updated in the customers table, it will automatically update in the orders table.