INFS2200 Chap.3 Database Constraints and State Transitions
Database Constraints and State Transitions
Constraints translate domain promises into central database enforcement. This chapter separates static rules that describe valid states from dynamic rules that govern legal transitions. It matches required values, uniqueness, identity, references and row conditions to declarative SQL mechanisms, then reserves triggers or controlled procedures for history-sensitive logic.
Original state diagrams and adversarial tests show how to verify both accepted and rejected operations.
Static validity operates at several levels. NOT NULL protects required attributes, UNIQUE prevents repeated combinations, PRIMARY KEY designates identity, FOREIGN KEY preserves a reference, and CHECK expresses a row condition. Referential actions such as cascade or restriction add lifecycle policy to the relationship.
The action should follow whether child rows have independent meaning or must remain as history.
Dynamic validity asks whether a change is allowed even when the old and new values are individually valid. A status domain may allow DRAFT and CLOSED while forbidding a CLOSED record from returning to DRAFT. Transition-aware logic compares old and new states and is tested as a directed workflow.
Declarative enforcement comes first; procedural mechanisms are reserved for history or cross-row semantics.
Testing is part of the design. Each rule receives a passing row and a minimally different failing row. Lifecycle logic receives at least one permitted and one forbidden edge.
A failure should name the protected invariant clearly enough that a developer can distinguish invalid data, a missing parent and an illegal transition without weakening the rule.
Constraint timing affects what can be represented during a transaction. A multi-step change can pass through a temporary state that would be invalid if exposed as a committed database state.
The design must distinguish a rule that must hold after every statement from one that may be deferred until the transaction boundary. That choice cannot be used to excuse an invalid final state; it only determines when enforcement observes the unit of work.
Referential actions deserve a lifecycle argument.
Restricting deletion is appropriate when dependent history must keep its parent, cascading may be appropriate when a child has no independent meaning, and setting a reference to null requires that absence itself be allowed and meaningful. The SQL keyword follows the policy rather than replacing it.
For every action, test deletion, key modification and the effect on existing children.
Cross-row invariants expose the boundary of a simple CHECK. A condition such as total allocation across several rows not exceeding a budget depends on more than the row being written. It may require a transactionally safe procedure, trigger logic or a redesigned representation.
Concurrency matters: two individually plausible writes can jointly violate the limit unless the enforcement reads and protects the relevant state consistently.
A review should trace each business promise to exactly one authoritative enforcement point and then test bypass routes. Direct SQL, bulk loading and alternative applications should not be able to create states that the main interface rejects.
Error messages should identify the invariant without revealing sensitive data. This closes the path from domain sentence to mechanism, transaction behaviour and observable failure.
What this chapter covers
- 01
Static and dynamic constraints
- 02
NOT NULL, UNIQUE and CHECK
- 03
Primary and foreign keys
- 04
Referential actions
- 05
Transition rules and test pairs
Worked example · free
Protect a request lifecycle
- +1Use a primary key for request identity.
- +1Use CHECK for the priority domain.
- +1Use a foreign key for the customer reference.
- +2Use transition-aware logic for reopening.
- +1Design one passing and one failing test.
Key terms
- Static constraint
- A rule that must hold in every valid database snapshot.
- Dynamic constraint
- A rule controlling legal operations or state transitions.
- Referential integrity
- The requirement that a foreign-key value match an allowed parent key.
- Check constraint
- A row condition that must evaluate as valid for stored data.
Database Constraints and State Transitions FAQ
What is the difference between static and dynamic constraints?
Static constraints describe valid states, such as allowed values or required references. Dynamic constraints describe valid changes, such as forbidding a closed record from returning to an open state. A value can be statically valid but reached through an illegal transition.
When is a trigger justified?
Use a trigger when a rule depends on old and new values, related rows or transition history that declarative constraints cannot express cleanly. Keep simple domains and references declarative. Procedural code should solve a real scope problem rather than duplicate built-in integrity.
How should I test a constraint?
Create a minimally different passing and failing case. For transitions, test every allowed edge and at least one forbidden edge. The pair proves both acceptance and rejection behaviour. Include null or missing-parent cases when the rule makes them relevant.
Exam move
Translate every rule into a business sentence, classify its scope, choose the mechanism and design adversarial tests. Keep referential actions tied to lifecycle meaning, because cascade, restriction and nulling are policy decisions rather than syntax preferences. Build a table with four columns: invariant, static or dynamic scope, enforcement mechanism and test pair.
For lifecycle questions, sketch states as nodes and permitted operations as directed edges. Check that every trigger branch corresponds to that drawing. For foreign keys, explain what should happen to children when a parent changes or disappears. End each exercise by stating the invariant again in plain language; this catches technically valid SQL that protects the wrong business rule.