Data Integrity: Cascade Deletes & Default Values

A database is only as reliable as the quality of the data it stores. Without strict rules, invalid, orphaned, or duplicate records can corrupt your application's state. This chapter explores how to enforce data integrity in PostgreSQL using constraints, default values, and referential actions like cascade deletes.

1. Querying Array Data Types: Intuitive vs. Complex Syntax

When working with array data types in PostgreSQL (such as a skills column storing ['React', 'Node', 'PostgreSQL']), querying can be done in multiple ways. Some syntaxes are highly readable, while others can look overly complex and unintuitive.

The Naive/Complex Approach (Unintuitive Syntax)

PostgreSQL supports various array operators (like @> for "contains"). While powerful, this syntax can look confusing to developers unfamiliar with it:

-- An unintuitive way to check if 'React' is in the skills array
SELECT * FROM users WHERE skills @> ARRAY['React'::varchar];

Creator's Note: This syntax can look a bit strange—almost like a "bird's eye and beak" (@>). It is less readable and harder to maintain for developers reading your queries for the first time.

The Better Approach (Intuitive Syntax)

Using the ANY operator is much more readable and intuitive:

-- A clean, readable way to search within an array
SELECT * FROM users WHERE 'React' = ANY(skills);

This query reads naturally: "Select all users where 'React' matches any value inside the skills array."

2. The Core Purpose of Database Constraints

Constraints are rules enforced on data columns to prevent invalid data from being entered into the database.

Core Philosophy: Constraints prevent bad data.

Rather than relying solely on application-level validation (e.g., in Node.js, Python, or React), database constraints act as the ultimate line of defense. Even if a bug bypasses your backend validation, the database will reject any write operation that violates its schema rules.

3. Core PostgreSQL Constraints Explained

To design robust schemas, you must understand the primary constraints available in PostgreSQL:

4. Mental Model: Null vs. Undefined

Understanding how databases and programming languages handle missing values is crucial for data integrity.

Real-Life Analogy

• Null: Imagine an empty mailbox. The mailbox exists, it has an address, but there is currently nothing inside it. It is an explicit container holding "nothing."
• Undefined: Imagine there is no mailbox at all. You haven't even designated a place for mail to go.

Technical Distinction

• NULL (Database Level): Represents the deliberate absence of any value. It is a placeholder indicating that data is missing or unknown.
• Undefined (Application Level - e.g., JavaScript): Indicates that a variable has been declared but has not yet been assigned any value.

Note: Databases do not have an "undefined" state. A column value is either populated with valid data, contains an explicit NULL, or falls back to a defined DEFAULT value.

5. The Check Constraint

The CHECK constraint allows you to specify a condition that every row must satisfy. It is highly useful for domain-level validations, such as ensuring prices are positive or ages are above a certain threshold.

Code Example: Implementing a CHECK Constraint

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    discounted_price NUMERIC(10, 2),
    
    -- Ensure price is always greater than zero
    CONSTRAINT chk_positive_price CHECK (price > 0),
    
    -- Ensure discount price is lower than the original price
    CONSTRAINT chk_discount CHECK (discounted_price < price)
);

6. Default Values (Data Integrity)

A DEFAULT constraint automatically assigns a predefined value to a column when an INSERT statement does not explicitly provide one. This is essential for fields like registration timestamps, status flags, or default settings.

Code Example: Using Default Values

CREATE TABLE user_accounts (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    status VARCHAR(20) DEFAULT 'active', -- Defaults to 'active'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Defaults to system time
);

If you insert a record without specifying status or created_at:

INSERT INTO user_accounts (username, email) 
VALUES ('john_doe', 'john@example.com');

PostgreSQL automatically populates the row as:

• status: 'active'
• created_at: 2023-10-27 14:30:00 (current system time)

7. Foreign Keys & Cascade Deletes (On Delete Cascade)

When tables are linked via relationships, deleting a record in a parent table can leave orphaned records in child tables. PostgreSQL solves this using referential integrity actions.

The Problem: Orphaned Records

If a user with user_id = 1 is deleted, what happens to their blog posts in the posts table? If left unchecked, those posts will reference a user ID that no longer exists, causing application crashes and data corruption.

The Solution: ON DELETE CASCADE

By adding ON DELETE CASCADE to a Foreign Key constraint, PostgreSQL automatically deletes all associated child records when the parent record is deleted.

Code Example: Implementing Cascade Deletes

-- Parent Table
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL
);

-- Child Table with Cascade Delete
CREATE TABLE articles (
    article_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INT,
    
    -- Foreign Key with Cascade Delete
    CONSTRAINT fk_author
        FOREIGN KEY (author_id) 
        REFERENCES authors(author_id)
        ON DELETE CASCADE
);

8. Step-by-Step Evolution: Creating a Secure Table with Constraints

Let's look at how a table schema evolves from a naive, fragile design into a robust, production-ready table using constraints.

Phase 1: The Naive Approach (No Constraints)

This table allows duplicate emails, negative prices, missing usernames, and orphaned records.

CREATE TABLE enrollments (
    student_id INT,
    course_name VARCHAR(100),
    fee NUMERIC,
    email VARCHAR(100)
);

Phase 2: The Decoupled, Robust Production Approach

This design uses primary keys, unique constraints, check constraints, default values, and cascade deletes to guarantee absolute data integrity.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(150) NOT NULL,
    price NUMERIC(8, 2) NOT NULL DEFAULT 0.00,
    
    CONSTRAINT chk_positive_price CHECK (price >= 0)
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign Keys with Cascade Deletes
    CONSTRAINT fk_student 
        FOREIGN KEY (student_id) 
        REFERENCES students(student_id) 
        ON DELETE CASCADE,
        
    CONSTRAINT fk_course 
        FOREIGN KEY (course_id) 
        REFERENCES courses(course_id) 
        ON DELETE CASCADE
);

9. Real-World Pitfalls & Debugging Workflows

Even with constraints in place, you will encounter errors during development and production. Understanding how to debug these issues is key.

Scenario A: Unique Constraint Violation (23505)

• The Error: You attempt to insert a user with an email that already exists in the database.
• PostgreSQL Error Code: 23505 (unique_violation)
• Debugging Workflow:
  1. Inspect the error payload to identify the conflicting key (e.g., Key (email)=(john@example.com) already exists.).
  2. Implement an UPSERT (using ON CONFLICT) if you want to update the existing record instead of throwing an error:
     SQL

     Copy

     INSERT INTO students (student_name, email) 
     VALUES ('John Doe', 'john@example.com')
     ON CONFLICT (email) 
     DO UPDATE SET student_name = EXCLUDED.student_name;
     

Scenario B: Foreign Key Violation on Delete (23503)

• The Error: You try to delete a parent record, but PostgreSQL blocks it because child records exist and no cascade action was defined.
• PostgreSQL Error Code: 23503 (foreign_key_violation)
• Debugging Workflow:
  1. Check if the foreign key was defined with ON DELETE RESTRICT or ON DELETE NO ACTION (the default behavior).
  2. If you want child records to be deleted automatically, alter the table to add ON DELETE CASCADE.
  3. Alternatively, if you want to keep the child records but remove the association, use ON DELETE SET NULL:
     SQL

     Copy

     CONSTRAINT fk_author
         FOREIGN KEY (author_id) 
         REFERENCES authors(author_id)
         ON DELETE SET NULL
     

Summary and Key Takeaways

• Constraints Prevent Bad Data: Always enforce rules at the database level rather than relying solely on application-level validation.
• Array Querying: Use the intuitive 'value' = ANY(array_column) syntax instead of complex, hard-to-read array operators.
• Primary Keys: Every table should have a primary key (typically an auto-incrementing ID) to uniquely identify each row.
• Null vs. Undefined: NULL represents an explicit empty value (an empty mailbox), whereas Undefined represents the complete absence of a variable or container.
• Check Constraints: Use CHECK constraints to enforce business logic directly within the database schema (e.g., price > 0).
• Default Values: Use DEFAULT to automatically populate columns like timestamps or status flags when they are omitted during inserts.
• Cascade Deletes: Use ON DELETE CASCADE to automatically clean up child records when a parent record is deleted, preventing orphaned data.
• Set Null Alternative: Use ON DELETE SET NULL if you want to keep child records after a parent is deleted, clearing out only the relationship link.