Foreign Keys and Referential Actions

Concept

Referential integrity says a foreign key must resolve. The interesting question is what the DBMS should do when a parent row is deleted or its key updated, since either could break that rule. Referential actions are the answer, and choosing them is a design decision, not a technicality.

The five actions

How each behaves

  BEFORE
    departments        students
      CS  Computing      21 Meera  CS
      EC  Electronics    22 Ravi   CS
                         23 Anitha EC

  DELETE the CS department:

  RESTRICT / NO ACTION
      rejected. Two students still reference CS.
      Nothing changes.

  CASCADE
      departments: EC only
      students:    23 Anitha EC
      Meera and Ravi are GONE. Deleting one department
      deleted two students.

  SET NULL
      departments: EC only
      students:    21 Meera  (null)
                   22 Ravi   (null)
                   23 Anitha EC
      Requires dept_code to be nullable. If it is
      NOT NULL, this action cannot be used at all.

  SET DEFAULT
      students take the default department, which must
      itself exist or the operation fails.

Choosing the right action

Ask one question: can the child exist meaningfully without the parent?

The general rule: cascade delete only for weak entities and true parts. Anywhere else it is a loaded weapon — deleting one row can silently remove thousands, and cascades chain, so a cascade can trigger another cascade several tables deep.

The cascade chain problem

  customers --cascade--> orders --cascade--> order_lines
                              
                               --cascade--> payments

  DELETE one customer

    removes their orders
      removes every line of every order
      removes every payment of every order

  One statement, four tables, potentially thousands of rows,
  including financial records that should have been retained.

  This is why financial and audit data uses RESTRICT, and
  why "deletion" of a customer is usually a STATUS CHANGE
  rather than a DELETE at all.

Soft delete

For anything with history — customers, orders, patients, members — the usual production answer is not to delete at all. Add a status or a deleted_at column, hide the row from normal queries, and keep the referential structure intact. The data stays available for audit, and no cascade can ever fire.

Update actions and why they are usually unnecessary

If a primary key is chosen well it is stable, so it is never updated and ON UPDATE never fires. Needing ON UPDATE CASCADE is normally a sign that a changeable value was chosen as the key. Fix the key rather than automate the change.

Example

-- Illustration only, showing a deliberate action on each.
--
-- order_lines: a part of its order
--   FOREIGN KEY (order_no) REFERENCES orders (order_no)
--     ON DELETE CASCADE ON UPDATE RESTRICT
--
-- orders: financial records, never removed with the customer
--   FOREIGN KEY (cust_no) REFERENCES customers (cust_no)
--     ON DELETE RESTRICT ON UPDATE RESTRICT
--
-- employees: keep the employee when the manager leaves
--   FOREIGN KEY (manager_id) REFERENCES employees (emp_id)
--     ON DELETE SET NULL ON UPDATE RESTRICT

Common mistakes

• Using cascade delete everywhere because it removes errors. It removes the error message, not the problem, and it removes data with it.
• Declaring set null on a not null column. Contradictory — the action can never succeed.
• Forgetting cascades chain. Always trace the full graph before enabling one.
• Relying on the default. Products differ in what happens when no action is specified. Declare it explicitly.
• Deleting rows that should be retained. Financial, medical and audit rows are almost never truly deleted.

Exam and interview questions

1. List the five referential actions and describe each.
2. When is cascade delete appropriate, and when is it dangerous?
3. Why can set null not be used on a not null foreign key?
4. What is the cascade chain problem? Give an example.
5. Why does a well chosen primary key rarely need an update action?

Practice

1. Choose an action for each: invoice and invoice lines, student and enrolments, author and books, ward and admissions.
2. Trace what a cascade delete of one department would remove in the college schema of Phase 4b.
3. Rewrite a customer deletion as a soft delete and list the queries that must change.

Conclusion

Referential actions decide what happens to children when a parent goes. Cascade only where the child is genuinely part of the parent, restrict for anything with independent meaning, set null for optional relationships — and for data with history, prefer a status change over a delete.