Data Integrity Rules

Why This Matters

Data integrity rules are the backbone of every reliable database system—and they're exactly the kind of foundational concept that shows up repeatedly on exams. You're being tested on your understanding of how databases prevent bad data from corrupting entire systems, from duplicate records to orphaned rows to values that violate business logic. These rules work together to ensure that your data remains accurate, consistent, and meaningful across tables and relationships.

Don't just memorize a list of rule names. Know what problem each rule solves, how it's enforced (constraints, keys, triggers), and when you'd apply one rule versus another. Exam questions love to present scenarios where data integrity has been violated—your job is to identify which rule was broken and how to fix it. Master the underlying mechanisms, and you'll handle any FRQ or multiple-choice question thrown your way.

---

Uniqueness and Identification Rules

These rules ensure every record can be uniquely identified and retrieved. Without unique identification, databases become unsearchable chaos—imagine a library where every book has the same call number.

Entity Integrity Rule

• Primary keys must be unique and non-null—this guarantees every record has a distinct identifier that always exists
• Prevents duplicate records within a table by requiring each row to have a different primary key value
• Foundation for all relationships—foreign keys in other tables depend on reliable primary key identification

Key Integrity Rule

• Guarantees uniqueness across all primary key values—no two records can share the same identifier
• Supports efficient data retrieval because queries can locate exactly one record per key value
• Structural requirement for normalized database design; without it, joins and lookups become unreliable

Unique Constraint Rule

• Enforces uniqueness on non-primary-key columns—think email addresses or social security numbers
• Allows null values (unlike primary keys), so multiple records can have empty values in that column
• Implemented via $UNIQUE$ constraint in SQL, which creates an index to check for duplicates on insert/update

---

Relationship and Reference Rules

These rules maintain consistency between tables. When tables reference each other through keys, these rules prevent "dangling pointers" that reference data that doesn't exist.

Referential Integrity Rule

• Foreign key values must match an existing primary key or be null—no pointing to records that don't exist
• Prevents orphaned records by blocking deletions or updates that would break relationships
• Enforced through $ON DELETE$ and $ON UPDATE$ actions like $CASCADE$, $SET NULL$, or $RESTRICT$

Cardinality Integrity Rule

• Defines relationship types—one-to-one, one-to-many, or many-to-many between entities
• Ensures accurate modeling of real-world relationships (one customer has many orders, not the reverse)
• Implemented through table structure—junction tables for many-to-many, foreign key placement for one-to-many

---

Value Restriction Rules

These rules control what values can be stored in specific columns. Think of them as gatekeepers that reject invalid data before it enters the database.

Domain Integrity Rule

• Restricts values to a defined data type—integers, strings, dates, etc., as specified in the schema
• Enforces value ranges through data type limits (e.g., $TINYINT$ only holds 0–255)
• First line of defense against invalid data; the database engine rejects type mismatches automatically

Check Constraint Rule

• Specifies custom conditions that values must satisfy—e.g., $CHECK (age >= 18)$
• Enforces business rules at the database level rather than relying on application code
• Evaluated on every insert and update—if the condition fails, the operation is rejected

Null Integrity Rule

• Defines whether columns accept null values—enforced via $NOT NULL$ constraint
• Ensures critical fields are always populated—customer names, order dates, account numbers
• Clarifies data meaning—distinguishes between "unknown" (null) and "intentionally empty" (empty string)

---

Business Logic and Semantic Rules

These rules ensure data accurately represents real-world meaning and organizational requirements. They bridge the gap between raw storage and meaningful information.

Semantic Integrity Rule

• Ensures data reflects real-world entities accurately—a "birth date" shouldn't be in the future
• Enforces meaning-based constraints that go beyond simple data types or ranges
• Often implemented through triggers and stored procedures that validate complex conditions

Business Rule Integrity

• Encodes organizational policies directly in the database—discount limits, approval workflows, access rules
• Maintains compliance with company procedures regardless of which application accesses the data
• Supports decision-making by ensuring reports and queries reflect policy-compliant data

---

Quick Reference Table

---

Self-Check Questions

1. Which two integrity rules both enforce uniqueness, and what distinguishes how they handle null values?

2. A database allows a foreign key to reference a primary key that was just deleted, creating an orphaned record. Which integrity rule has been violated, and what $ON DELETE$ action would prevent this?

3. Compare and contrast domain integrity and check constraints—when would you use each, and can they work together on the same column?

4. An FRQ presents a scenario where a "quantity" field contains negative values and a "ship date" occurs before the "order date." Which integrity rules address each problem?

5. Explain how entity integrity, referential integrity, and cardinality integrity work together to maintain a properly structured one-to-many relationship between $Customers$ and $Orders$ tables.