Referential Integrity in SQL and DBMS
Introduction
Referential Integrity is the database principle that keeps relationships between rows valid. When a child row contains a Foreign Key value, that value must normally match an existing candidate key value in the referenced parent table. If the relationship is optional, the Foreign Key may instead be NULL when its columns allow NULL.
Consider customers and orders. If order 5001 stores customer_id = 101, customer 101 must exist. An order pointing to customer 999 while no such customer exists is a broken reference. A Foreign Key constraint prevents that invalid state and controls what may happen when a referenced customer is updated or deleted.
Referential Integrity is not only about rejecting bad inserts. It covers the full relationship lifecycle: creating parents and children, changing keys, deleting records, loading data, preserving history, handling optional associations, and coordinating transactions. This tutorial explains parent and child tables, orphan records, referential actions, composite and self-referencing keys, migrations, performance, distributed systems, testing, and practical schema design.
Why Referential Integrity Is Important
Without enforced relationships, a database can contain orders with missing customers, invoice lines with missing invoices, payments assigned to nonexistent accounts, or employees linked to departments that were deleted. Queries then return incomplete results, reports disagree, cleanup becomes risky, and application code must guess what an invalid reference means.
Application validation is useful for friendly messages, but it cannot be the final authority. Several applications, imports, scheduled jobs, administrative scripts, and concurrent requests may write to the same tables. A check performed before an insert can become outdated before the write commits. The database is the shared boundary capable of validating every transaction consistently.
A Foreign Key also documents the schema. It tells developers and tools that one column refers to a specific candidate key, clarifies dependency order, and enables the DBMS to enforce delete and update policies. Referential Integrity therefore supports correctness, communication, safe maintenance, and trustworthy analytics.
Parent and Child Tables
The referenced table is commonly called the parent, and the table containing the Foreign Key is called the child. These names describe one relationship, not permanent roles for an entire table. An orders table can be the child of customers and simultaneously the parent of order items.
CUSTOMERS
customer_id PRIMARY KEY
|
+---- ORDERS
customer_id FOREIGN KEY
order_id PRIMARY KEY
|
+---- ORDER_ITEMS
order_id FOREIGN KEY
A parent does not need to have children. Referential Integrity normally permits a customer with zero orders. It restricts the child side: every non-NULL child reference must point to an eligible parent value. Business rules that require at least one child need additional design or transactional logic because a standard Foreign Key does not enforce that reverse minimum.
Primary Keys and Foreign Keys
A Primary Key identifies one row in its own table. A Foreign Key stores values that reference a Primary Key or another eligible unique candidate key. The data types and column structure must be compatible according to the selected DBMS.
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
order_total DECIMAL(12, 2) NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);
The constraint states that every order's customer ID must be found in customers.customer_id. It does not require the child value to be unique, so many orders can reference the same customer. Adding UNIQUE to orders.customer_id would change the cardinality to at most one order per customer, which is usually not intended.
Valid and Invalid Relationships
Assume customers 101 and 102 exist. Orders containing customer IDs 101, 101, and 102 are valid because every reference has a matching parent. An order containing 999 is invalid if customer 999 does not exist.
INSERT INTO orders (order_id, customer_id, order_total)
VALUES (5004, 999, 49.95);
-- Rejected when customer 999 does not exist
The normal parent-first workflow creates the customer before inserting the order.
INSERT INTO customers (customer_id, customer_name)
VALUES (101, 'John');
INSERT INTO orders (order_id, customer_id, order_total)
VALUES (5001, 101, 125.00);
Both statements should normally run in a transaction when they form one business operation. If order creation fails, the application can decide whether the newly created customer should also roll back.
Orphan Records
An orphan record is a child row whose referenced parent no longer exists. An order with customer_id = 999 is orphaned when there is no customer 999. Orphans often appear in legacy databases that lack Foreign Keys, after constraints were disabled for a load, or when cleanup scripts delete parents without checking children.
Orphans are dangerous because joins can silently hide them. An inner join between orders and customers omits the orphaned orders, causing reports to undercount revenue or operations to overlook records. A left join may reveal NULL parent columns, but every query must then decide how to handle a state that should not have existed.
Referential Integrity prevents new orphans and controls parent deletion. Existing orphans must be detected and resolved before a Foreign Key can be added safely.
Integrity During INSERT, UPDATE, and DELETE
On insert, the database checks every non-NULL Foreign Key against the referenced key. On a child update, changing the Foreign Key to a nonexistent parent is rejected. On a parent update or delete, the DBMS evaluates dependent child rows and applies the configured referential action.
The action may block the parent change, propagate it, clear the child reference, or assign a default. Choosing the action is a business decision about lifecycle ownership. It should not be selected merely to make a delete statement convenient.
Stable surrogate parent keys reduce the need for key updates. Business attributes such as customer number or email can change while child tables continue to reference an immutable customer_id. This design narrows referential decisions primarily to creation and deletion.
Common Referential Actions
| Action | Effect on dependent child rows | Typical use |
|---|---|---|
| RESTRICT | Blocks the parent change when children exist | Prevent deletion of referenced master data |
| NO ACTION | Requires integrity by the applicable check time | Common default behavior |
| CASCADE | Propagates parent delete or key update | Children share the parent's lifecycle |
| SET NULL | Clears the child Foreign Key | Optional relationship should survive |
| SET DEFAULT | Assigns the defined default reference | DBMS-supported fallback relationship |
Support and exact semantics vary by product. In particular, RESTRICT and NO ACTION may behave similarly for immediate constraints but differ in systems that support deferred checking. Always confirm behavior in the project's DBMS and test it in a transaction.
RESTRICT and NO ACTION
A restrictive policy preserves children by refusing to delete or alter a referenced parent. If a customer has orders, deleting that customer fails until the application explicitly reassigns, archives, or removes the dependent records.
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
ON DELETE RESTRICT
This policy is appropriate when child rows have independent legal, financial, or historical value. It forces the caller to make the lifecycle decision visible instead of losing related data automatically.
NO ACTION also requires the relationship to be valid at the point the constraint is checked. Some systems check immediately; systems with deferrable constraints may allow temporary inconsistency inside a transaction and validate at statement or commit time. Product-specific documentation determines whether the two actions differ operationally.
ON DELETE CASCADE
ON DELETE CASCADE automatically deletes dependent child rows when the parent is deleted. It is useful when the child has no independent meaning and is wholly owned by the parent, such as temporary order draft lines, private configuration entries, or association rows.
CREATE TABLE order_items (
order_item_id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
CONSTRAINT fk_items_order
FOREIGN KEY (order_id)
REFERENCES orders (order_id)
ON DELETE CASCADE
);
Deleting an order removes its items, so no item can outlive its owning order. Cascade becomes dangerous when applied without ownership analysis. A delete near the top of a deep graph may remove large amounts of data, trigger extensive locking, and bypass a user's expectation that history will remain. Financial transactions, audit records, and shared entities usually require preservation rather than automatic deletion.
ON DELETE SET NULL and SET DEFAULT
SET NULL preserves the child row but removes its relationship when the parent is deleted. The Foreign Key columns must permit NULL, and the resulting unassigned state must be meaningful.
CREATE TABLE support_tickets (
ticket_id BIGINT PRIMARY KEY,
assigned_employee_id BIGINT NULL,
subject VARCHAR(200) NOT NULL,
CONSTRAINT fk_ticket_assignee
FOREIGN KEY (assigned_employee_id)
REFERENCES employees (employee_id)
ON DELETE SET NULL
);
If an employee row is removed, the ticket remains and becomes unassigned. This would be wrong for a mandatory ownership relationship, where a missing assignee is invalid.
SET DEFAULT replaces the child value with its declared default when supported. The default must itself satisfy the Foreign Key, such as a valid "unassigned" parent row. Artificial fallback rows can obscure meaning, so NULL or an explicit reassignment workflow is often clearer. DBMS support for SET DEFAULT should be verified.
ON UPDATE CASCADE
ON UPDATE CASCADE propagates a changed referenced key into child rows. It can help when a mutable natural key is used for relationships, such as a corrected department code.
FOREIGN KEY (department_code)
REFERENCES departments (department_code)
ON UPDATE CASCADE
Primary and alternate identifiers used widely as relationship targets are usually easier to manage when immutable. A surrogate parent ID lets a department's visible code change without modifying relationship columns. Even where update cascade is supported, a high-volume change can touch many rows and create locks, replication traffic, and audit complexity.
Optional and Mandatory Relationships
A Foreign Key determines whether a non-NULL value must reference a parent. Nullability determines whether the relationship may be absent. Both constraints are needed to express mandatory participation from the child side.
-- Mandatory relationship
customer_id BIGINT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
-- Optional relationship
referrer_customer_id BIGINT NULL,
FOREIGN KEY (referrer_customer_id) REFERENCES customers (customer_id)
Without NOT NULL, an order can contain no customer while still satisfying the Foreign Key. This is not a failure of referential checking; NULL means there is no asserted reference to validate. Designers must separately decide whether absence is valid.
A mandatory child reference does not require every parent to have a child. For example, every order can require a customer while many customers have no orders.
One-to-Many and One-to-One Relationships
A typical one-to-many relationship places a non-unique Foreign Key in the many-side table. Several orders can store the same customer ID. Referential Integrity guarantees that the shared ID exists; it does not limit how many times it appears.
A one-to-one or one-to-zero-or-one relationship adds uniqueness on the child Foreign Key.
CREATE TABLE employee_profiles (
profile_id BIGINT PRIMARY KEY,
employee_id BIGINT NOT NULL UNIQUE,
biography VARCHAR(1000),
FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);
The Foreign Key guarantees a valid employee, and UNIQUE guarantees that no employee appears in more than one profile. The constraints work together to express cardinality from the child side.
Many-to-Many and Junction Tables
A many-to-many relationship is represented through a junction table containing Foreign Keys to both parents. Each constraint independently protects one side, while a composite Primary Key or Unique Key prevents the same pair from appearing twice.
CREATE TABLE student_courses (
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
enrolled_at TIMESTAMP NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students (student_id),
FOREIGN KEY (course_id) REFERENCES courses (course_id)
);
The row cannot reference a missing student or course, and the same enrollment cannot be duplicated. If a surrogate junction ID is added, the pair should normally retain a UNIQUE (student_id, course_id) constraint unless duplicate relationships are meaningful.
Self-Referential Integrity
A table can reference itself. An employee may reference another employee as manager, or a category may reference another category as parent.
CREATE TABLE employees (
employee_id BIGINT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
manager_id BIGINT NULL,
CONSTRAINT fk_employee_manager
FOREIGN KEY (manager_id)
REFERENCES employees (employee_id)
ON DELETE SET NULL
);
The Foreign Key prevents a manager ID that does not exist. It does not prevent every invalid hierarchy. A row may be able to reference itself unless another rule blocks it, and a multi-row cycle such as A managing B while B manages A requires recursive validation beyond an ordinary Foreign Key.
Recursive hierarchies need an explicit root policy, cycle protection, deletion behavior, and query strategy. Referential Integrity proves that referenced nodes exist; it does not prove the graph is logically well formed.
Composite Foreign Keys
When the parent candidate key contains several columns, the child can reference the complete key. Column order and data types must correspond to the referenced unique definition.
CREATE TABLE company_employees (
company_id BIGINT NOT NULL,
employee_number VARCHAR(30) NOT NULL,
employee_name VARCHAR(100) NOT NULL,
PRIMARY KEY (company_id, employee_number)
);
CREATE TABLE timesheets (
timesheet_id BIGINT PRIMARY KEY,
company_id BIGINT NOT NULL,
employee_number VARCHAR(30) NOT NULL,
work_date DATE NOT NULL,
CONSTRAINT fk_timesheet_employee
FOREIGN KEY (company_id, employee_number)
REFERENCES company_employees
(company_id, employee_number)
);
A timesheet must reference an employee number in the same company. Composite Foreign Keys are accurate but make child schemas, indexes, and joins wider. A surrogate parent key can simplify references while the composite natural key remains uniquely constrained.
NULL handling for composite Foreign Keys follows the DBMS and match semantics. Avoid partially populated composite relationships unless their exact meaning and enforcement behavior are understood.
Parent Keys Must Be Eligible and Unique
A Foreign Key must reference columns that identify at most one parent row, normally a Primary Key or eligible Unique Key. Referencing a non-unique parent column would be ambiguous because several parents could match the same child value.
Referenced and referencing columns should use compatible types, lengths, character sets, collations, and signedness as required by the DBMS. Subtle mismatches can prevent constraint creation or produce comparison behavior different from application expectations.
A Foreign Key can reference a natural alternate key, but mutable natural values create update coupling. Stable surrogate keys are often chosen as relationship targets, while natural identifiers remain protected through Unique constraints.
Referential Integrity Versus Other Integrity Types
| Integrity type | Purpose | Common mechanism |
|---|---|---|
| Entity Integrity | Every row has a valid unique identity | PRIMARY KEY |
| Referential Integrity | Relationships point to existing eligible rows | FOREIGN KEY |
| Domain Integrity | Attribute values belong to the valid domain | Data type, NOT NULL, CHECK |
| Business Integrity | Application-specific invariants remain true | UNIQUE, CHECK, transactions, controlled logic |
A Foreign Key does not make its child value unique, validate an order total, prevent hierarchy cycles, or require a parent to have children. Strong schemas combine several constraint types, each responsible for the rule it can express accurately.
Referential Integrity and JOIN Results
Foreign Keys improve trust in joins because every asserted reference has a matching parent. An inner join between orders and customers should not lose orders due to missing customer rows, assuming the constraint is enabled, trusted, and all relevant rows satisfy it.
The constraint does not guarantee that a query uses the correct join columns or relationship. Nor does it prevent optional Foreign Keys from being NULL. Query authors still need to choose inner or outer joins based on required and optional relationships.
Some optimizers can use trusted constraints to reason about joins, but performance benefits vary. The primary reason for a Foreign Key remains correctness, not a promise that every join will become faster.
Finding Existing Orphan Records
Legacy data should be checked before adding a constraint. A left join can find children with no parent.
SELECT o.order_id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c
ON c.customer_id = o.customer_id
WHERE o.customer_id IS NOT NULL
AND c.customer_id IS NULL;
NOT EXISTS expresses the same audit clearly.
SELECT o.order_id, o.customer_id
FROM orders AS o
WHERE o.customer_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM customers AS c
WHERE c.customer_id = o.customer_id
);
Each orphan needs a business resolution: create the legitimate missing parent, reassign the child, clear an optional relationship, quarantine the record, or delete it under an approved retention policy. Inventing placeholder parents merely to satisfy the constraint can hide data-quality problems.
Adding a Foreign Key to Existing Data
Before adding a Foreign Key, verify parent uniqueness, child data types, NULL policy, duplicate parent business data, and orphaned child references. Resolve invalid data and index requirements, then add a named constraint using the product's supported DDL.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);
Large production tables may require an online validation strategy or a staged process that prevents new violations while existing rows are checked. Some products can create an unvalidated constraint and validate it later; others may scan and lock the table during creation. A constraint marked untrusted or left unvalidated weakens optimizer assumptions and operational confidence, so the rollout should finish with full validation.
Transactions and Deferred Checking
Transactions let related writes succeed or fail together. Parent creation followed by child creation is naturally valid when both occur in one transaction. Deleting children before their parent also works with a restrictive Foreign Key when the transaction executes the statements in that order.
BEGIN;
DELETE FROM order_items WHERE order_id = 5001;
DELETE FROM orders WHERE order_id = 5001;
COMMIT;
Some databases support deferrable constraints that are checked at transaction commit rather than after each statement. This can help with cyclic relationships or complex key swaps that are temporarily inconsistent during the transaction but valid at completion. Support, syntax, and defaults vary significantly. Deferred checking should be used deliberately because failures appear later and transactions may hold more work before rejection.
Bulk Loading and Data Migration
Bulk loads should usually insert parent records before children. Migration tooling can stage source data, validate mappings, load dimensions or master entities, and then load dependent transactions. This ordering maintains integrity throughout the process.
Disabling constraints may speed certain loads, but it transfers responsibility to the migration. A failed job can leave orphans, and re-enabling a constraint without validating existing data creates false confidence. Prefer product-supported bulk techniques that retain or restore trusted validation, and run explicit orphan checks before declaring the migration complete.
When legacy identifiers are converted to new surrogate keys, maintain a mapping table from old parent keys to new IDs. Populate child Foreign Keys through that mapping and prove that every required source relationship maps exactly once.
Soft Deletes and Historical Records
Soft deletion changes a status such as is_deleted instead of removing the row. Because the parent row still exists, the Foreign Key remains technically valid. Business logic may nevertheless need to prevent new children from referencing inactive parents. A standard Foreign Key does not check parent status.
Historical orders, invoices, payments, and audit events often need their parent identity preserved even when the current customer or account is deactivated. Soft deletion or immutable historical snapshots can retain that context. Hard deletion with cascade would destroy evidence that the business may be required to keep.
Referential validity and active eligibility are different rules. The database can enforce existence with a Foreign Key, while application or carefully designed database logic enforces whether a parent in its current state can accept a new relationship.
Referential Integrity Across Microservices
A database Foreign Key works inside a database boundary where the DBMS controls both tables and one transaction. Microservices often own separate databases, so an orders service cannot create a normal Foreign Key to a customers table owned by another service.
Cross-service relationships use distributed consistency techniques: validated API calls, replicated reference data, event-driven projections, durable messages, idempotency, reconciliation jobs, and compensating actions. Temporary inconsistency may be expected. The order service might retain a customer identifier even if the customer service is temporarily unavailable or later deactivates the account.
This does not make integrity unimportant; it changes the enforcement mechanism and consistency model. Within each service's database, local Foreign Keys should still protect relationships the service owns. Across services, teams need explicit contracts, ownership, failure handling, and repair processes.
Performance and Foreign Key Indexing
The referenced parent key is indexed or otherwise uniquely enforced. The child Foreign Key is not automatically indexed in every DBMS. An index on child Foreign Key columns often improves joins, parent deletion checks, updates, and queries that retrieve children for one parent.
CREATE INDEX ix_orders_customer_id
ON orders (customer_id);
Without a useful child index, deleting or updating a parent may require scanning a large child table to prove that no dependents exist. Cascades may also become expensive. Index design should consider column order, selectivity, write overhead, and actual query patterns rather than blindly creating duplicates of existing composite indexes.
Foreign Key checks add write work, but the cost is usually justified by preventing corruption. Measure high-volume workloads and optimize schema and batch design instead of removing integrity constraints as a first response.
Common Design Mistakes
The first mistake is omitting Foreign Keys because application code checks references. That leaves imports, scripts, races, and future applications able to create orphans. Another mistake is deleting the parent first under a restrictive policy, rather than deleting or reassigning children in a controlled transaction.
Applying CASCADE everywhere can turn a small administrative action into a large destructive operation. Conversely, using RESTRICT everywhere can block legitimate ownership cleanup and force fragile manual scripts. Each relationship needs its own lifecycle decision.
Other errors include allowing NULL for a mandatory relationship, assuming a Foreign Key makes the child unique, expecting it to require every parent to have children, referencing mutable business keys without an update plan, mismatching data types, and ignoring child-side indexes. Referential Integrity protects existence, not every business rule.
Testing Referential Integrity
A focused test suite should insert a valid child reference and confirm success, then insert an unknown parent value and confirm failure. A mandatory Foreign Key should reject NULL, while an optional relationship should accept NULL but still reject a nonexistent non-NULL value.
Test parent deletion and key updates for every configured action. A restrictive relationship must block the change, cascade must affect only intended rows, and SET NULL must preserve the child in a valid state. Composite keys need cases where one component differs or is missing. Self-references need root, self-cycle, and deeper-cycle scenarios according to business rules.
Run transaction, concurrency, migration, soft-delete, and bulk-load tests. Finish with an orphan-detection query over realistic data. A test that exercises only application validation does not prove the database constraint exists or behaves correctly.
Referential Integrity Design Workflow
Begin with the business relationship and identify which entity owns the referenced identity. Select the parent's Primary Key or eligible alternate key, define the child column or columns, and decide whether participation is mandatory.
Define the relationship and cardinality
-> identify parent and child roles
-> select an eligible unique parent key
-> create compatible child Foreign Key columns
-> choose NULL or NOT NULL
-> choose delete and update actions
-> add supporting indexes where justified
-> clean and validate existing data
-> test inserts, updates, deletes, and concurrency
-> document ownership and lifecycle behavior
Revisit the design when retention, ownership, service boundaries, or deletion policies change. Relationship constraints should evolve with the domain rather than be bypassed by ad hoc scripts.
Complete E-Commerce Example
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
order_total DECIMAL(12, 2) NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
ON DELETE RESTRICT
);
CREATE TABLE order_items (
order_item_id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
CONSTRAINT fk_items_order
FOREIGN KEY (order_id)
REFERENCES orders (order_id)
ON DELETE CASCADE,
CONSTRAINT fk_items_product
FOREIGN KEY (product_id)
REFERENCES products (product_id)
ON DELETE RESTRICT
);
An order cannot reference a missing customer. A customer with orders cannot be hard-deleted accidentally. Deleting an order removes its owned line items, while a product used by an item cannot be deleted without an explicit preservation or reassignment policy. Different actions reflect different ownership and historical requirements within the same schema.
Interview Questions and Answers
What is Referential Integrity? It is the rule that every asserted Foreign Key reference must match an existing eligible parent key, unless the optional relationship is represented by permitted NULL values.
What is an orphan record? A child row whose referenced parent does not exist.
Does a Foreign Key make a column unique? No. Repeated child references are allowed unless a separate Primary Key or Unique constraint prevents them.
What is the difference between mandatory and optional relationships? A mandatory child Foreign Key is normally NOT NULL; an optional Foreign Key permits NULL.
When is CASCADE appropriate? When child rows are wholly owned by the parent and should not survive it.
Can a Foreign Key reference a Unique Key? Yes, when the referenced columns form an eligible unique candidate key under the DBMS rules.
Why index Foreign Key columns? A child-side index can improve joins and the checks required for parent updates or deletes.
Can Foreign Keys enforce relationships across microservice databases? Not directly. Cross-service consistency requires contracts, messages, validation, and reconciliation outside one database transaction.
Practical Review Checklist
- Define the relationship and its cardinality before writing DDL.
- Reference a Primary Key or eligible Unique Key.
- Keep parent and child column definitions compatible.
- Use NOT NULL when the child relationship is mandatory.
- Choose delete and update actions from lifecycle ownership.
- Use CASCADE only when dependent rows truly share the parent lifecycle.
- Preserve financial, legal, audit, and historical records appropriately.
- Validate existing data and remove or resolve orphan records.
- Index child Foreign Keys where workload and delete checks benefit.
- Test valid, invalid, NULL, update, delete, and composite cases.
- Verify constraints after bulk loads and migrations.
- Distinguish referential existence from active business eligibility.
- Document cross-service consistency when a database FK is impossible.
- Never rely on application checks as the only local integrity control.
Key Takeaway
Referential Integrity ensures that database relationships do not point to missing rows. A Foreign Key connects the child to an existing Primary Key or eligible Unique Key in the parent, while nullability and referential actions define whether the relationship is optional and what happens during updates or deletion.
REFERENTIAL INTEGRITY
= VALID FOREIGN KEY REFERENCES
+ CONTROLLED PARENT CHANGES
+ NO ORPHANED CHILD ROWS
Entity Integrity protects row identity, Domain Integrity protects valid attribute values, and Referential Integrity protects relationships. When Foreign Keys, nullability, lifecycle actions, transactions, indexes, and tests accurately reflect the business model, the database remains consistent even as applications and data pipelines evolve.