Course contentsShow
← Computer Science
Lesson 1659 of 2,87238. Database Transactions and IndexingFree lesson

Integrity Constraints and Consistency

Understand how foreign keys, unique constraints, and checks enforce consistency during transactions.

Integrity Constraints and Consistency

What you'll learn: How foreign keys, unique constraints, and checks work behind the scenes to keep your database consistent during transactions.

The Role of Integrity Constraints

When you learned about Consistency in transactions, you saw that databases must preserve invariants—rules that must always be true. Integrity constraints are the database's enforcers: they're rules baked into your schema that automatically reject operations violating those invariants.

Think of integrity constraints like guardrails on a bridge. Even if someone tries to drive off the edge (submit bad data), the guardrails prevent disaster.

Three Key Constraint Types

Foreign Keys ensure referential integrity—every reference must point to something real. If you try to insert an order with customer_id = 999 but customer 999 doesn't exist, the database blocks it. This prevents "orphan" records that reference nonexistent data.

Unique Constraints guarantee no duplicates in specified columns. Trying to insert a second user with email alice@example.com when Alice already exists? Rejected. This maintains entity uniqueness across transactions.

Check Constraints enforce business rules directly: age >= 0, salary > 0, status IN ('active', 'pending', 'closed'). They catch logic errors before they corrupt your data.

How They Protect Consistency

During a transaction, these constraints are checked at key moments—typically when you INSERT or UPDATE data, and definitely before COMMIT. If any constraint fails, the entire transaction rolls back atomically, leaving the database unchanged. This is how the database maintains consistency even when concurrent users are making changes—invalid states simply cannot be committed.

Key Takeaway: Integrity constraints (foreign keys, unique, and checks) are automatic consistency guardians that prevent transactions from violating database rules, ensuring every committed state is valid.