Referential Integrity and Cascading Actions

Learning outcomes

Referential integrity ensures that references between relations point to valid target rows—or are absent only when the relationship is optional. Foreign keys enforce this principle, but the design is incomplete until update and deletion behavior matches the lifecycle of the domain.

Foreign keys encode references

WorkshopHub's WorkOrder must reference an existing Asset:

asset_id INTEGER NOT NULL,FOREIGN KEY (asset_id)  REFERENCES asset(asset_id)

The database rejects a WorkOrder whose asset_id does not exist.

Foreign keys enforce target existence, not full business semantics

The FK proves only that the referenced Asset row exists. It does not prove:

• the asset belongs to the same tenant;
• the asset was active at the work-order date;
• the asset is eligible for the requested service;
• the relationship was historically valid at that point in time.

Additional rules may be required.

RESTRICT / NO ACTION

For historical operational data, blocking parent deletion is often safest:

FOREIGN KEY (asset_id)  REFERENCES asset(asset_id)  ON DELETE RESTRICT

A WorkOrder should not silently disappear because someone removed an Asset record.

CASCADE

ON DELETE CASCADE deletes dependent child rows automatically when the parent is deleted. It is appropriate for data whose lifecycle is truly owned by the parent.

Example: temporary session and session-token rows may be safely cascaded. Historical invoices or work orders usually should not be.

SET NULL

SET NULL retains the child row but removes the reference:

ON DELETE SET NULL

This is valid only if the child is meaningful without the parent. It would contradict the rule “every WorkOrder belongs to exactly one Asset.”

SET DEFAULT

Replacing a deleted parent with a default reference is rarely correct unless the business has a real default such as an explicit “Unknown legacy source” entity. Never use a dummy default merely to avoid FK errors.

Updates to referenced keys

If business keys are referenced directly, changing them can require cascading updates. Stable surrogate keys reduce this pressure:

asset_id       -- stable internal keyserial_number  -- correctable business identifier

Composite foreign keys

If a parent is identified by a composite candidate key, a child reference may also be composite:

FOREIGN KEY (manufacturer_id, serial_number)REFERENCES asset_registry(manufacturer_id, serial_number)

All columns must refer to the same candidate key and should represent one semantic reference.

Self-referencing integrity

Technician mentorship:

mentor_technician_id INTEGER,FOREIGN KEY (mentor_technician_id)  REFERENCES technician(technician_id)

The FK prevents references to nonexistent technicians but not cycles such as A mentors B and B mentors A. Cycle prevention is a higher-order rule.

Historical preservation

If a Technician leaves the company, deleting the row may break historical WorkOrderAssignments. Better options include:

• active = false;
• employment end date;
• separate Person/Employment structures;
• anonymization of selected attributes while preserving the referenced identity.

Soft deletion and foreign keys

A soft-deleted parent still exists physically, so the FK remains valid. But application queries must respect lifecycle status. Referential integrity and “currently usable” are different concepts.

Tenant integrity

A simple FK may allow a WorkOrder in Tenant A to reference an Asset in Tenant B if both share one global table. One strong pattern is composite tenant-scoped keys:

UNIQUE (tenant_id, asset_id)FOREIGN KEY (tenant_id, asset_id)  REFERENCES asset(tenant_id, asset_id)

This encodes ownership boundary into the relationship.

Orphan detection in legacy data

Before adding FKs to an existing schema, find orphan rows:

SELECT wo.*FROM work_order AS woLEFT JOIN asset AS a  ON a.asset_id = wo.asset_idWHERE a.asset_id IS NULL;

Clean or repair the data before enforcing the constraint.

WorkshopHub deletion policy example

Practice: choose the referential action

Summary and next lesson

Foreign keys preserve valid references, but referential actions must reflect lifecycle, retention, and ownership semantics. The next lesson turns to row-level business rules expressed through CHECK constraints and defaults.

References

• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• PostgreSQL documentation for foreign keys and referential actions.
• SQLite documentation for foreign key support.