Navigation
Focuses on maintaining data accuracy and reliability through the use of constraints.
What if a simple typo in a database allowed a user to withdraw $-\$1,000,000$ from an ATM, effectively 'printing' money? Data integrity is the invisible shield that prevents digital anarchy by ensuring every byte of information follows strict, unbreakable rules.
In database design, Data Integrity refers to the accuracy, completeness, and reliability of data. We enforce this using Constraints. The three most common 'local' constraints are: 1. NOT NULL, which ensures a column cannot be empty; 2. UNIQUE, which guarantees all values in a column are distinct (like an ID or email); and 3. CHECK, which validates that data meets a specific logical condition. For example, a CHECK constraint might ensure that a column is always . These rules act as the first line of defense, rejecting 'bad' data before it ever touches the disk.
Let's create a table for a school portal. We need to ensure every student has an email and a valid grade level.
1. Define the table: `CREATE TABLE Students (...)` 2. Apply NOT NULL to `student_name` so we always know who they are. 3. Apply UNIQUE to `email` so two students can't share one login. 4. Apply CHECK to `grade_level` to ensure the value is between 9 and 12: .
Quick Check
If a column has a UNIQUE constraint, can it also have a NOT NULL constraint?
Answer
Yes. A Primary Key is actually a combination of both: it must be unique and cannot be empty.
Data rarely lives in a single table. Referential Integrity ensures that relationships between tables remain consistent. This is managed via the Foreign Key (FK). A Foreign Key in one table points to a Primary Key (PK) in another. If Table A (Orders) has a Foreign Key pointing to Table B (Customers), the database will prevent you from adding an order for a customer that doesn't exist. It also prevents you from deleting a customer if they still have active orders. This prevents 'orphan records'—data that points to nothing, causing system crashes.
Imagine an online store with a `Products` table and an `Inventory` table.
1. `Products` has a PK called `product_id`. 2. `Inventory` uses `product_id` as a Foreign Key. 3. If you try to add stock for `product_id = 500`, but the `Products` table only goes up to 499, the system throws an error. 4. This ensures that always maps to a real, existing product.
Quick Check
What is an 'orphan record' in the context of referential integrity?
Answer
An orphan record is a row in a child table that references a non-existent row in a parent table.
Professional systems use complex CHECK constraints to enforce business rules. These can involve mathematical comparisons or set memberships. For instance, in a banking app, you might have a constraint where . Constraints can also be 'Composite,' meaning they involve multiple columns. A common example is ensuring that a `start_date` is always chronologically before an `end_date`. By moving this logic into the database layer rather than the application code, we ensure that no matter how a user accesses the data (via web, mobile, or API), the rules are universally enforced.
A library system must ensure that books are not returned before they are borrowed and that fines are never negative.
1. Table: `Loans` 2. Constraint 1: `CHECK (return_date >= borrow_date)` 3. Constraint 2: `CHECK (daily_fine_rate > 0)` 4. If a bug in the software tries to set a `return_date` to '1990-01-01' for a book borrowed in 2023, the database engine itself will block the transaction, preserving the truth of the data.
Which constraint would you use to ensure that a 'discount_percentage' column never exceeds 100?
If you try to delete a record in a 'Parent' table that is still referenced by a 'Child' table, what happens under standard referential integrity?
A single table can have multiple UNIQUE constraints but only one Primary Key.
Review Tomorrow
In 24 hours, try to explain the difference between Domain Integrity and Referential Integrity to a peer.
Practice Activity
Sketch a schema for a 'Music Streaming App'. Identify which columns need NOT NULL, which need UNIQUE, and where a Foreign Key must connect 'Songs' to 'Albums'.