Constraint Identification: Defining the Rules of Data Integrity

Data without constraints is chaos. Imagine a database where customer ages can be negative, order totals can exceed available inventory, employees can report to themselves, and accounts can have balances that violate business policies. Such a system would be worse than useless—it would actively mislead.

Constraints are the rules that separate valid data from invalid data. They encode the business logic that ensures every record in the database represents something real, possible, and acceptable within the organization's context.

Constraint identification is the systematic process of discovering, documenting, and specifying these rules. It ensures that the database enforces data integrity—preventing errors at the source rather than detecting them after damage is done.

A constraint is a rule that restricts the values that can be stored in a database. Constraints ensure that data conforms to business reality and organizational policies.

Why Constraints Matter:

Data Quality: Constraints prevent invalid data from entering the database. An age cannot be -5. A hire date cannot be in the future. An order cannot reference a non-existent customer.

Business Rule Enforcement: Constraints encode business policies. A credit limit cannot be exceeded. An employee cannot be their own manager. A product cannot be sold below minimum price.

Referential Integrity: Constraints maintain relationships between entities. Every order must belong to a valid customer. Every line item must reference a valid product.

Consistency Guarantees: Constraints prevent inconsistent states. If a warehouse is deleted, what happens to its inventory? Constraints define and enforce these rules.

Domain constraints define the set of valid values for each attribute. They are the most fundamental constraints because they establish what data is acceptable at the most basic level.

Types of Domain Constraints:

Constraint Extraction from Requirements:

Domain constraints emerge from various sources:

From Forms:

• Dropdown menus define enumerations
• Input field lengths suggest max character limits
• Required field markers indicate NOT NULL
• Input masks reveal format patterns

From Documents:

• Data dictionaries specify types and lengths
• Data standards define formats (ISO date, currency codes)
• Policy documents constrain valid values

From Interviews:

• "The quantity ordered must be at least 1"
• "Status can be Pending, Approved, or Rejected"
• "Email is required for all online customers"

From Existing Systems:

• Legacy database column definitions
• Application validation code
• Error messages indicating rejected values

Key constraints ensure that entity instances can be uniquely identified and that no two instances share the same identity.

Types of Key Constraints:

Complex Uniqueness Constraints:

Uniqueness is not always global. Some constraints are conditional:

Scoped Uniqueness:

• ProductCode is unique within a Supplier (not globally)
• Employee email is unique within a Department
• Room number is unique within a Building

Implementation: Composite unique constraint on (ScopeKey, ValueColumn)

Temporal Uniqueness:

• Only one active price for a product at any time
• Only one current manager per department at any point

Implementation: Requires checking overlap of date ranges

Conditional Uniqueness:

• SSN is unique BUT only when not null
• External ID is unique BUT only for external customers

Implementation: Partial indexes or CHECK + UNIQUE combination

Referential integrity constraints maintain the consistency of relationships between entities. They ensure that references always point to valid, existing records.

The Core Principle:

If entity A references entity B (foreign key relationship), then:

• The referenced record in B must exist (on insert/update of A)
• When the referenced record in B is modified/deleted, A must be handled appropriately

Referential Constraint Specification:

For each relationship, document:

RELATIONSHIP: Order references Customer

Foreign Key: Order.CustomerID → Customer.CustomerID

Mandatory: Yes (every order must have a customer)

On Delete of Customer:
  - Action: RESTRICT
  - Reason: Cannot delete customers with order history
  - Alternative: Soft-delete customer, keep orders accessible

On Update of Customer.CustomerID:
  - Action: CASCADE (if surrogate key changes are possible)
  - Reason: Orders should follow customer identity
  - Note: Surrogate keys should not change; this is precautionary

Business Rule:
  - Orders can only be placed by Active customers
  - When customer becomes Inactive, existing orders remain valid

Complex Referential Scenarios:

Self-Referential:

• Employee.ManagerID → Employee.EmployeeID
• Must prevent cycles (A manages B manages A)

Multiple References:

• Order.BillingAddressID → Address.AddressID
• Order.ShippingAddressID → Address.AddressID
• Different cascade rules may apply to each

Polymorphic References:

• Comment.EntityType + Comment.EntityID can reference Order, Product, or Customer
• Cannot be enforced by simple foreign key; requires application or trigger logic

Semantic constraints encode business meaning and logic that goes beyond simple domain or referential rules. They ensure data makes sense in the context of the business.

Categories of Semantic Constraints:

Semantic Constraint Examples:

CONSTRAINT: Order Total Consistency

Rule: The sum of OrderItem.LineTotal for an Order must 
      equal Order.SubTotal + Order.Tax + Order.Shipping - Order.Discount

Enforcement: Trigger or application logic
            (too complex for CHECK constraint)

Violation Action: Reject transaction; log discrepancy

---

CONSTRAINT: Manager Level Hierarchy

Rule: An employee's manager must have a higher Level than the employee
      (Level 1 reports to Level 2+; Level 2 reports to Level 3+)

Exception: CEO (highest level) has no manager (ManagerID is NULL)

Enforcement: Trigger on INSERT and UPDATE of Employee

---

CONSTRAINT: Inventory Availability

Rule: Cannot ship more than available inventory
      Shipment.Quantity ≤ Product.QuantityOnHand - Product.QuantityReserved

Enforcement: CHECK constraint with subquery (if supported) 
             or trigger with reservation system

Concurrency Note: Requires locking or optimistic concurrency control

Temporal constraints govern the time-related aspects of data—validity periods, sequences, durations, and date-based rules.

Types of Temporal Constraints:

Temporal Overlap Prevention:

A common requirement is preventing overlapping time periods. For example:

• Only one room booking at a time
• Only one price effective for a product at a time
• Only one assignment per employee at a time

Non-Overlap Constraint:

For records with (EntityID, StartDate, EndDate), ensure no two records for the same EntityID have overlapping periods:

Two periods (S1, E1) and (S2, E2) overlap if: S1 < E2 AND S2 < E1

Implementation Approaches:

1. Trigger that queries for overlaps before insert/update
2. EXCLUDE constraint (PostgreSQL) with range types
3. Application-level validation

Temporal Validity Tracking:

Many entities track when facts are valid:

• Transaction Time: When the fact was recorded in the database
• Valid Time: When the fact was true in the real world

A bi-temporal database tracks both, enabling queries like:

• "What was the customer's address on January 1st?" (valid time)
• "What address did we have on file when we processed this order?" (transaction time)

Cardinality constraints specify the minimum and maximum number of entity instances that can participate in a relationship.

Cardinality Notation:

Cardinality is expressed as (min, max) where:

• min = minimum number of related instances (0 = optional, 1+ = mandatory)
• max = maximum number of related instances (1 = single, N or * = unlimited, or specific number)

Enforcing Minimum Cardinality:

Maximum cardinality of 1 is straightforward (unique constraint). Minimum cardinality > 0 (mandatory participation) is more challenging:

For 'each X must have at least one Y':

• Cannot simply use NOT NULL on foreign key (that's wrong direction)
• Requires either:
  • Deferred constraint checking (validate after transaction commits)
  • Trigger that prevents deleting the last Y or requires Y creation with X
  • Application logic enforcement

Example Constraint:

CONSTRAINT: Order Must Have Items

Rule: Every Order must have at least one OrderItem

Problem: How to insert Order before OrderItems exist?

Solution 1: Deferred constraint
  - Begin transaction
  - Insert Order
  - Insert OrderItems
  - Commit (constraint checked here)

Solution 2: Business rule enforcement
  - Application ensures Order + first Item created together
  - Prevent deleting last OrderItem
  - Prevent creating Order without Items