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

Constraints are embedded throughout requirements sources. Systematic extraction ensures none are missed.

Constraint Extraction Techniques:

Trigger Word Analysis: Scan documents for constraint indicator words:

• "Must" / "Must not" → Mandatory constraint
• "Should" → Suggested but not enforced (clarify if it becomes a constraint)
• "Cannot" / "Never" → Prohibition constraint
• "Only when" / "Only if" → Conditional constraint
• "At least" / "At most" → Cardinality or range constraint
• "Before" / "After" → Temporal sequence constraint
• "Unique" / "One per" → Uniqueness constraint

Negative Case Inquiry: During interviews, ask explicitly:

• "What values would be invalid for this field?"
• "What scenarios should the system prevent?"
• "What happens if someone tries to do X?"
• "Are there exceptions to this rule?"

Edge Case Exploration:

• What if the quantity is zero? Negative?
• What if the dates are equal?
• What if the reference doesn't exist?
• What if there are no related records?

Constraints must be documented in a structured format that enables clear communication, validation, and eventual implementation.

Example Constraint Documentation:

CONSTRAINT: BR-ORD-003

Name: Order Cannot Exceed Credit Limit

Category: Semantic Constraint

Description:
  The total value of a customer's open orders (not yet paid) 
  cannot exceed their assigned credit limit.

Formal Expression:
  FOR ALL Customer c:
    SUM(Order.Total WHERE Order.CustomerID = c.CustomerID 
                    AND Order.Status IN ('Pending', 'Confirmed', 'Shipped'))
    ≤ c.CreditLimit

Entities Involved:
  - Customer (CreditLimit attribute)
  - Order (Total, Status, CustomerID attributes)

Enforcement Level:
  - Database trigger on Order INSERT and UPDATE
  - Application pre-check before order submission

Violation Response:
  - Reject order creation
  - Return error message: "Order exceeds available credit. 
    Available credit: $X. Order total: $Y."

Exceptions:
  - Customers with CreditLimit = NULL have no limit (premium customers)
  - Manager override with documented approval (log override)

Source:
  - Finance Policy FP-2023-04, Section 3.2
  - Interview: CFO, 2024-01-15

Priority: Critical

Test Cases:
  - Order within limit → Accept
  - Order exactly at limit → Accept
  - Order $0.01 over limit → Reject
  - Customer with no limit → Always accept
  - Manager override → Accept with logging

Constraint identification completes the requirements gathering phase. With data requirements, functional requirements, and constraints, you have a comprehensive specification of what the database must store, what it must do, and what rules it must enforce.

Module Conclusion:

With this page, you have completed the Requirements Gathering module. You now understand how to:

1. Conduct user interviews — Engaging stakeholders to capture tacit knowledge and business context
2. Analyze documents — Extracting requirements from forms, reports, policies, and existing systems
3. Specify data requirements — Defining entities, attributes, domains, and relationships
4. Specify functional requirements — Documenting operations, queries, reports, and integrations
5. Identify constraints — Capturing business rules, validation requirements, and integrity constraints

These skills form the foundation for all subsequent database design activities. A well-gathered set of requirements prevents costly errors, reduces rework, and ensures the final database serves its intended purpose.