Answer.

Related tables (for example, "orders" and "customers") have existed in relational databases since their inception, but in the early stages, integrity control had to be implemented manually through programming logic. With the development of SQL, built-in constraints (FOREIGN KEY) and automatic actions (CASCADE) emerged.

History of the issue:

Initially, databases required mechanisms to maintain integrity to avoid "orphaned" records (for example, an order without an existing customer). FOREIGN KEY became the standard, and CASCADE options automated synchronization on deletions and updates.

Problem:

Without cascading actions, deleting or updating a row in the primary table leads to errors or "orphaned" data. Offloading this task to the application often results in maintenance complexity and the risk of incidents during bulk operations. Incorrect use of cascading actions can cause avalanches of deletions or violate business logic.

Solution:

Applying FOREIGN KEY with ON DELETE CASCADE and ON UPDATE CASCADE allows for automatic integrity maintenance and correct synchronization of related tables. In complex scenarios (for example, if logging actions is also needed), triggers are used.

Code example:

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Name NVARCHAR(100)
);

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  Amount DECIMAL(10,2),
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Key features:

ON DELETE CASCADE — automatic deletion of child records when the parent record is deleted.
ON UPDATE CASCADE — automatic updating of the foreign key value.
In complex scenarios, implementation through triggers for custom actions is possible.

Trick questions.

Is cascading deletion always the best practice for all relationships?

No: for "historical" data or archival information, cascading deletion can lead to the loss of valuable information. It is important to understand the business value of each type of relationship.

Does ON UPDATE CASCADE work if the foreign key is not part of the PRIMARY KEY of the parent table?

In most DBMSs, the foreign key must reference a unique or PRIMARY KEY for cascading support. Otherwise, the command will not work.

Can a cascade chain exceed the allowable nesting limit (recursion), and what will happen?

Yes: in large cascades, it is possible to exceed the depth limit (for example, in SQL Server — 32). This will lead to an error and roll back the operation.

Typical mistakes and anti-patterns

Cascading deletion with weak control — potential loss of important data.
Ignoring the need to log bulk changes.
Constructing triggers without considering performance (for example, leading to infinite recursion).

Real-life example

Negative case

In the supplier and order accounting system, ON DELETE CASCADE was applied — clients mistakenly deleted an important supplier, automatically deleting all orders and losing supply history. Restoring data became impossible.

Pros:

Minimum manual work.

Cons:

Loss of business history.

Positive case

Used ON DELETE SET NULL, plus a trigger for logging — even after deleting a customer record, the order history was preserved (status updated to obsolete), preventing accidental bulk deletion.