Essential Database Constraints

Why This Matters

Database constraints are the guardrails that keep your data clean, consistent, and reliable—and they're absolutely central to how relational databases work. When you're tested on database systems, you're being evaluated on your understanding of data integrity, referential relationships, and validation mechanisms. These aren't just syntax rules to memorize; they represent fundamental design decisions that prevent corrupted data, broken relationships, and invalid entries from ever entering your system.

Think of constraints as a contract between your database and the real world it models. Primary keys ensure every entity is uniquely identifiable. Foreign keys maintain logical relationships between tables. Check and domain constraints enforce business rules at the data level. As you study these, don't just memorize what each constraint does—understand which integrity problem each one solves and when you'd choose one over another in a database design scenario.

---

Identity and Uniqueness Constraints

These constraints answer a fundamental question: how do we ensure each record is distinct and identifiable? Without them, you'd have no reliable way to reference specific rows or prevent duplicate entries.

Primary Key Constraint

• Uniquely identifies each record—this is the foundation of the relational model, giving every row a guaranteed distinct identifier
• Cannot contain NULL values; every record must have a complete, non-empty primary key to ensure reliable identification
• Limited to one per table, though it can be composite (spanning multiple columns) when a single column isn't sufficient for uniqueness

Unique Constraint

• Enforces distinct values across a column or column set—similar to primary keys but without the "identity" role
• Allows NULL values by default, though most systems permit only one NULL per unique column to maintain logical consistency
• Supports multiple unique constraints per table, unlike primary keys—useful for alternate candidate keys like email addresses or SSNs

Entity Integrity Constraint

• Guarantees every table has a valid primary key—this is a meta-constraint that enforces the existence and validity of primary keys
• Ensures uniqueness and non-nullability of the primary key, combining both requirements into one integrity rule
• Fundamental to the relational model; without entity integrity, you cannot reliably distinguish between records

---

Relationship and Referential Constraints

These constraints maintain the logical connections between tables. In a relational database, data is spread across multiple tables—these rules ensure those relationships stay valid.

Foreign Key Constraint

• Links tables together by referencing the primary key of another table, establishing parent-child relationships
• Enforces referential integrity—prevents "orphaned" records where a child row points to a non-existent parent
• Can accept NULL values unless restricted, allowing optional relationships where a record may or may not have an associated parent

Referential Integrity Constraint

• Ensures foreign keys always point to valid primary keys—the broader principle that foreign key constraints implement
• Prevents orphaned records through restriction, or handles them through cascading actions (CASCADE DELETE, CASCADE UPDATE)
• Maintains logical consistency across the entire database by guaranteeing that relationships reflect real-world connections

---

Value Validation Constraints

These constraints control what data can be entered into specific columns. They're your first line of defense against invalid, incomplete, or nonsensical data.

Not Null Constraint

• Prohibits NULL values in a column, ensuring mandatory fields always contain data
• Essential for required information like names, IDs, or any field where missing data would break application logic
• Simple but powerful—can be applied to any column and is often combined with other constraints

Check Constraint

• Validates data against custom conditions—enforces rules like $\text{age} \geq 0$ or $\text{salary} > 0$
• Supports complex expressions including ranges, formats, and logical conditions using Boolean operators
• Catches invalid data at insertion time, preventing bad entries rather than cleaning them up later

Domain Constraint

• Defines permissible values based on data type plus additional restrictions—think of it as type + rules
• Encompasses data type, format, and range requirements for a column (e.g., a "grade" column accepting only 'A' through 'F')
• Broader than check constraints—domain constraints represent the complete set of valid values for an attribute

---

Default Values and Complex Rules

These constraints handle special cases: what happens when no value is provided, and how do you enforce rules that span multiple tables?

Default Constraint

• Assigns automatic values when no explicit value is provided during INSERT operations
• Simplifies data entry and ensures columns have meaningful values—common for timestamps, status fields, or counters
• Works with any data type—can default to strings, numbers, dates, or even function results like $\text{CURRENT\_TIMESTAMP}$

Assertion

• Enforces database-wide conditions that must always evaluate to true—goes beyond single-table constraints
• Handles complex business rules involving multiple tables or aggregate conditions (e.g., "total inventory must never be negative")
• Rarely implemented in practice—most commercial DBMS systems don't support assertions due to performance overhead; triggers often substitute

---

Quick Reference Table

---

Self-Check Questions

1. Which two constraints both enforce uniqueness, and what key difference determines when you'd use each one?

2. A DELETE operation fails with a "referential integrity violation" error. Explain what caused this and name two ways to resolve it.

3. Compare and contrast check constraints and domain constraints—when would you use each, and how do they overlap?

4. You're designing a table where the "email" column must be unique but the "phone" column is optional. Which constraints would you apply to each, and why?

5. An FRQ asks you to enforce a rule that "no department's total salary budget can exceed $1 million." Why can't a check constraint handle this, and what constraint type would you theoretically use?