FOREIGN KEY

Declaration turns a modeled reference into an enforced rule

A foreign key declares that a child reference must match an accepted parent key under the engine's applicable rules. A campus employee can reference a department through department_id. A diagram may express that intended association, but the DDL must identify the actual child columns, parent table and referenced columns. Merely using the same column name does not create enforcement.

This topic focuses on declaration mechanics rather than treating every relationship line as automatic database behaviour. State which table owns the reference and what a valid parent identity means. If the model requires each employee to have a department, the child property also needs an appropriate required-value rule. Referential matching and mandatory presence are different constraints.

The child column list maps to the parent list

A table-level FOREIGN KEY clause lists child roles and the corresponding referenced parent roles in order. For a department key composed of campusid and departmentid, both roles belong in the complete reference. A child with department_id 10 on Campus 2 must not accidentally match Department 10 on Campus 1 just because the local number is the same.

List arity and correspondence matter. Two child roles need two correctly ordered referenced roles, with supported compatible definitions. A composite declaration is not equivalent to two independent single-column references to unrelated candidate values. The full parent identity must exist together, which is exactly what the identifying pair expresses in the campus model.

sql
CONSTRAINT fk_employee_department
  FOREIGN KEY (campus_id, department_id)
  REFERENCES departments (campus_id, department_id)
  ON DELETE RESTRICT

Parent identity needs a supported target key

The examples reference a parent primary key, which provides a clear unique required identity. Appropriate unique keys can also serve supported references under the engine's rules. MySQL has historical and version-specific behaviour around nonstandard referenced keys, so it is inaccurate to present every legacy extension as a portable design recommendation.

Choose a supported unique parent identity deliberately and verify the target version's requirements. Matching index availability, column ordering and storage-engine support all matter. A parent column containing unique values in today's sample is not the same as a declared supported key that protects future state. The reference should rely on the actual enforced definition rather than a coincidental three-row observation.

Type and comparison compatibility must be checked

Child and parent roles need compatible definitions under the target engine. Integer size and signedness can matter, and character references can depend on supported character-set and collation compatibility. A diagram showing both roles as a generic identifier does not prove the physical declarations meet those rules. Inspect the actual columns rather than infer compatibility from similar labels.

The exercises use matching integer definitions to keep that contract visible. A more complex schema using text codes needs its own exact definitions and native checks. Do not silently cast away a modeling mismatch in a query and claim the foreign key is established. A SELECT join can match transformed values without creating or validating a persistent reference constraint.

NULL matching does not establish mandatory participation

For an ordinary optional single-column reference in the illustrated MySQL form, NULL does not require a matching parent value. That allows an employee whose department has not been assigned under an optional relationship policy. It does not claim that a parent row with a NULL primary key exists, and it does not turn the relationship into mandatory participation.

Composite NULL rules depend on the supported match semantics. MySQL's ordinary behaviour should not be generalized into every product's MATCH FULL or other modes. The composite exercise instead makes both roles required so incomplete parent identities cannot enter the accepted child table. State the chosen policy rather than relying on ambiguous partly missing pairs.

Referential actions describe parent changes

ON DELETE CASCADE removes matching child rows when the referenced parent deletion is accepted under that rule. RESTRICT or relevant NO ACTION behaviour can block a parent change under the product's enforcement timing. SET NULL requires child columns and the model to permit the resulting missing reference. Each action encodes a domain policy, not a generic performance preference.

ON UPDATE actions concern changes to referenced key values under supported semantics. They are different from updating an unrelated parent description such as department_name. The exact set of supported actions and timing differs by engine. Do not assume SET DEFAULT or deferred checking works merely because the phrase appears in another product's documentation.

Adding a reference must address existing child state

A populated child table can contain proposals whose parent identity is absent. Adding an enforced foreign key with normal validation requires a compatible current state under the target's rules. Resolve orphaned facts through an accepted domain workflow rather than deleting them arbitrarily or disabling checks to force the alteration to pass.

A diagnostic anti-existence query can help identify missing parents, but it is not itself a foreign key. It observes supplied rows at one point and does not protect later writes. Under concurrent traffic, preparation and enforcement need a consistent transition policy. The lab fixtures are isolated and single-session, so they do not claim to establish every production validation or locking guarantee.

Naming supports deliberate maintenance

A descriptive constraint name makes the protected association easier to identify in catalog output and maintenance scripts. In MySQL, dropping a named reference uses ALTER TABLE child DROP FOREIGN KEY constraint_name. Removing the foreign-key constraint and removing a supporting index are different operations; inspect what remains rather than assuming one keyword always removes every related structure.

Dropping the reference changes accepted future writes even if no current row changes. Applications may rely on rejected orphan proposals or automatic actions, so maintenance is part of the shared data contract. A migration review should identify those consumers before treating a foreign-key removal as a harmless way to make a failing import succeed.

Storage engine and object kind affect enforcement

MySQL/InnoDB supports the relevant persistent reference behaviour. Other storage engines and older inline-reference syntax can differ, including cases where written syntax does not establish the intended rule. Accepted CREATE text alone is therefore weaker evidence than a real invalid child write being rejected. Verify the actual definition and behaviour on the engine being claimed.

Temporary tables also have restrictions that make them unsuitable proof of native persistent foreign keys in this tutorial. These exercises therefore create fixed disposable databases with persistent InnoDB parent and child tables on the isolated lab server. Their temporary input tables only supply synthetic fixture values. This keeps native enforcement separate from a query mirror or an ignored temporary declaration.

The simple exercise isolates optional references

The introductory task copies supplied departments and employees into known disposable parent and child tables. A required employee identity is separate from a nullable department reference. Valid employees match accepted departments, while an employee with NULL department remains permitted by the stated optional policy. A later nonmissing unknown department is rejected through the actual reference.

Each fixture resets only its own named disposable child and parent rows in dependency order before loading new inputs. This is an authorized laboratory arrangement, not an instruction to clear application tables. The prepared content migration stores the task and reference code as teaching content; it does not execute that laboratory reset against the application's shared database.

The composite exercise verifies scoped cascading

The medium task uses departments keyed by campusid and departmentid and children carrying the same complete pair. It deletes one specified parent pair and reads the surviving employees. Departments with the same local number on another campus are separate parents, so their employees must remain. This exposes incorrect single-role matching and overly broad cleanup.

Negative cases attempt a child whose complete parent pair is absent. An existing department_id elsewhere is not enough. Empty input and a fixture without the selected parent exercise harmless absence under the requested delete predicate. Recorded results establish the supported native InnoDB behaviour on the compatibility engine, not exact MySQL execution or every possible referential action.

Keep native evidence attached to its actual target

A useful structural illustration labels owner keys, child references and the exact action being tested. The notes, declarations and expected outputs should preserve the same campus scope. Otherwise a technically valid SQL clause can enforce a different relationship from the one students were taught to model.

The recorded MariaDB lab checks include real persistent InnoDB references and selected rejected writes, clearly labeled as compatibility-engine evidence. Exact MySQL/provider execution, other native engines, production concurrency and application migration application remain unverified. Earlier recorded native-action checks remain separate historical evidence; these new exercise cases do not silently expand them into a claim that every referential action or deployment scenario has passed.

References

• MySQL 8.4 Foreign key constraints