The Language Databases Use to Say No
A constraint is a database's way of refusing to accept invalid data. No application code required. No runtime check needed. The database simply will not allow a row that violates the rule, regardless of which client sent the request, which developer wrote the code, or whether the code was correct. Constraints are the database's immune system. They catch bugs that application code misses, enforce rules that span multiple services, and keep data valid 24 hours a day without human intervention.
Codd specified constraints as a fundamental part of the relational model. They evolved from simple primary key uniqueness in 1970 to declarative CHECK expressions in the 1980s to full trigger-based active databases in SQL-92. Each addition gave schemas more expressive power to encode business rules directly in the data layer.
Primary Keys: The Identity Foundation
A primary key is a column or set of columns whose values uniquely identify each row. No duplicates. No nulls. Codd made this a non-negotiable requirement: without unique row identification, the relational model's operations break down. The debate about what to use as a primary key has never fully resolved.
Natural keys use real-world identifiers: email address, Social Security Number, ISBN. They seem convenient because the identifier already exists in the business domain. They break when the real world changes: emails get recycled, SSNs get reassigned in edge cases, ISBN formats have changed multiple times. Surrogate keys are artificial: auto-incrementing integers or UUIDs generated by the database. They are stable, meaningless, and boring. Production experience overwhelmingly favors surrogate keys.
The best primary key is one that never needs to change. Natural keys change when business rules change. Surrogate keys never change because they carry no meaning. Boring is good in primary key design.
Foreign Keys: The Integrity Promise
A foreign key is a constraint that guarantees a column's value exists in another table's primary key. An order's customer_id must point to a real customer. A comment's post_id must point to a real post. Without foreign keys, nothing prevents an order from referencing customer #99999 who was deleted three months ago. The application might handle this case. The direct database query bypassing the application will not.
Foreign keys also define CASCADE behavior: what happens to child records when a parent is deleted. CASCADE deletes children automatically, useful for dependent data with no independent meaning. SET NULL nullifies the reference, preserving the child record without a parent. RESTRICT prevents deletion if children exist, encoding the business rule that a customer with outstanding orders cannot be removed. These are not database settings. They are business rules encoded in the schema.
Startups frequently skip foreign keys to move faster. The technical debt accumulates silently until the first orphaned record causes a production incident. The fix at scale is painful: adding foreign keys to large tables with existing violations requires cleaning up the data first, which requires understanding what the orphans mean, which requires interviews with people who have long since left the company.
CHECK Constraints: Domain Integrity
CHECK constraints enforce rules within a single column or across multiple columns in the same row. Age must be between 0 and 150. Status must be one of ACTIVE, SUSPENDED, or CLOSED. Price must be positive. End date must be after start date. These rules are simple but their enforcement is powerful: they apply to every write from every client, regardless of what the application code does.
The defense-in-depth principle says: validate in the frontend, validate in the API, validate in the service layer, AND validate in the database. The database check is the one that cannot be bypassed. Even a direct psql connection, a migration script, or a bulk import must satisfy the CHECK constraint. In financial systems, an order with a negative quantity or a trade with a commission exceeding 100% is not just a bug, it is a potential regulatory violation. CHECK constraints prevent it unconditionally.
UNIQUE Constraints and Triggers
UNIQUE constraints enforce that no two rows share the same value in the constrained column. Email addresses must be unique. Confirmation numbers must be unique. Cabin assignments on a specific deck must be unique. Composite UNIQUE constraints scope uniqueness to combinations: (deck, cabin_number) allows Deck A and Deck B to each have cabin '101' but prevents two assignments of the same cabin on the same deck.
Triggers, standardized in SQL-92, are stored procedures that fire automatically in response to INSERT, UPDATE, or DELETE events. They enable active databases: schemas that respond to changes without any application involvement. Audit log triggers capture every modification with the old value, new value, timestamp, and user. Materialized aggregate triggers keep a denormalized count column in sync with a child table. The rule of thumb that emerged from decades of trigger abuse: use triggers for auditing and integrity enforcement, never for business logic. Business logic in triggers is invisible to code reviews, impossible to test in isolation, and creates cascading side effects that are nightmarish to debug.
- Add a foreign key index every time you add a foreign key column. Databases do not always create it automatically, and a missing FK index causes slow JOIN queries.
- Use CHECK constraints for any column with a finite set of valid values. Enums change, CHECK constraints do not require application code changes.
- UNIQUE constraints answer hidden product questions: can two users share an email? Can soft-deleted records release their unique slot? Ask before writing the constraint.
- Triggers for audit logs are legitimate and powerful. Triggers for business logic are a maintenance trap.
The Real Cost of Missing Foreign Keys
Orphaned records are the most common and most expensive consequence of skipping foreign keys. An orphaned record references a parent that no longer exists: an order referencing a deleted customer, a comment referencing a deleted post, a permission referencing a deleted role. The records are not obviously broken. They pass application-level validation. They appear in counts and aggregates. They surface as 'user not found' errors in user-facing features weeks or months after the parent was deleted. Finding and cleaning up orphans in a large database without foreign keys requires writing custom queries to identify them, auditing each one to understand why it exists, and deciding whether to delete or reassign them. This work is orders of magnitude more expensive than adding the foreign key constraint in the first place.
Startups frequently skip foreign keys with the justification that 'we move fast and constraints slow us down.' The speed benefit is real during early development when the data model changes rapidly. The cost accumulates silently until the codebase matures and the orphan cleanup work begins. A practical compromise: add foreign keys when the schema stabilizes. Accept that early-stage development without foreign keys creates technical debt, but consciously pay it down as the schema matures.
Constraint Design Patterns for Specific Scenarios
Soft deletes with unique constraints require careful design. A UNIQUE(email) constraint on a users table prevents two active users from sharing an email. But soft-deleted users should be able to release their email for reuse, or a new user with the same email should be allowed even while the old user's record persists. Two approaches: change the unique constraint to UNIQUE(email, deleted_at) (allowing the same email once per deletion timestamp), or use a partial unique index UNIQUE(email) WHERE deleted_at IS NULL (unique among active users only). The partial index is usually the cleaner solution.
Time-bounded uniqueness follows the same pattern: a cabin can only be assigned to one passenger at a time, but the same cabin can be assigned to different passengers on different voyages. UNIQUE(cabin_id, voyage_id) expresses this constraint. For date-range overlap prevention, CHECK constraints alone are insufficient (they only validate the current row). A deferrable constraint or a trigger that checks for overlapping ranges in existing rows is required.
Constraints in Schema Design Interviews
- Always mention which columns need foreign keys. Interviewers watch for this to distinguish candidates who think about integrity from those who only think about structure.
- Explain CASCADE decisions: 'I use RESTRICT here because deleting a customer with outstanding orders should be a business decision, not an automatic database cascade.'
- Mention partial unique indexes for soft-delete scenarios. It shows production experience with the specific problems soft deletes create.
- UNIQUE constraints on natural identifiers (email, username, order number) are often forgotten. List them explicitly.
- The question 'what constraints would you add to this schema?' has a specific answer format: primary key, foreign keys with CASCADE rules, UNIQUE constraints, CHECK constraints on domain validity, NOT NULL on required columns.
The Real Cost of Missing Foreign Keys
Orphaned records are the most common and most expensive consequence of skipping foreign keys. An orphaned record references a parent that no longer exists: an order referencing a deleted customer, a comment referencing a deleted post, a permission referencing a deleted role. The records are not obviously broken. They pass application-level validation. They appear in counts and aggregates. They surface as 'user not found' errors in user-facing features weeks or months after the parent was deleted. Finding and cleaning up orphans in a large database without foreign keys requires writing custom queries to identify them, auditing each one to understand why it exists, and deciding whether to delete or reassign them. This work is orders of magnitude more expensive than adding the foreign key constraint in the first place.
Startups frequently skip foreign keys with the justification that 'we move fast and constraints slow us down.' The speed benefit is real during early development when the data model changes rapidly. The cost accumulates silently until the codebase matures and the orphan cleanup work begins. A practical compromise: add foreign keys when the schema stabilizes. Accept that early-stage development without foreign keys creates technical debt, but consciously pay it down as the schema matures.
Constraint Design Patterns for Specific Scenarios
Soft deletes with unique constraints require careful design. A UNIQUE(email) constraint on a users table prevents two active users from sharing an email. But soft-deleted users should be able to release their email for reuse, or a new user with the same email should be allowed even while the old user's record persists. Two approaches: change the unique constraint to UNIQUE(email, deleted_at) (allowing the same email once per deletion timestamp), or use a partial unique index UNIQUE(email) WHERE deleted_at IS NULL (unique among active users only). The partial index is usually the cleaner solution.
Time-bounded uniqueness follows the same pattern: a cabin can only be assigned to one passenger at a time, but the same cabin can be assigned to different passengers on different voyages. UNIQUE(cabin_id, voyage_id) expresses this constraint. For date-range overlap prevention, CHECK constraints alone are insufficient (they only validate the current row). A deferrable constraint or a trigger that checks for overlapping ranges in existing rows is required.
Constraints in Schema Design Interviews
- Always mention which columns need foreign keys. Interviewers watch for this to distinguish candidates who think about integrity from those who only think about structure.
- Explain CASCADE decisions: 'I use RESTRICT here because deleting a customer with outstanding orders should be a business decision, not an automatic database cascade.'
- Mention partial unique indexes for soft-delete scenarios. It shows production experience with the specific problems soft deletes create.
- UNIQUE constraints on natural identifiers (email, username, order number) are often forgotten. List them explicitly.
- The question 'what constraints would you add to this schema?' has a specific answer format: primary key, foreign keys with CASCADE rules, UNIQUE constraints, CHECK constraints on domain validity, NOT NULL on required columns.