How does the FOREIGN KEY constraint maintain referential integrity between tables?

The FOREIGN KEY constraint maintains referential integrity between tables by ensuring that a value in one table (the child table) corresponds to a valid value in another table (the parent table). Here’s how it works:

1. Linking Tables: A FOREIGN KEY in the child table references the PRIMARY KEY in the parent table. This establishes a relationship between the two tables.

2. Data Validation: When a record is inserted or updated in the child table, the database checks that the value in the FOREIGN KEY column exists in the referenced PRIMARY KEY column of the parent table. If it does not exist, the operation is rejected.

3. Cascading Actions: FOREIGN KEY constraints can also define cascading actions. For example:

   • ON DELETE CASCADE: If a record in the parent table is deleted, all corresponding records in the child table are automatically deleted.
   • ON UPDATE CASCADE: If the PRIMARY KEY value in the parent table is updated, the corresponding FOREIGN KEY values in the child table are automatically updated.

4. Preventing Orphan Records: By enforcing these rules, the FOREIGN KEY constraint prevents orphan records in the child table, ensuring that every record has a valid reference to the parent table.

Here’s an example of defining a FOREIGN KEY constraint:

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

In this example, customer_id in the orders table is a FOREIGN KEY that references customer_id in the customers table, maintaining referential integrity between the two tables.