Constraints and Data Integrity

# CHAPTER 19

Constraints and Data Integrity #

1. Introduction #

2. Learning Objectives #

• Prevent missing data using NOT NULL.

• Apply fallback values using DEFAULT.

• Review the UNIQUE constraint.

• Build complex validation logic using CHECK constraints.

• Alter existing tables to add defensive constraints.

3. The NOT NULL Constraint #

CREATE TABLE users (
    user_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(150) NOT NULL,    -- This column can never be blank!
    password_hash TEXT NOT NULL,
    phone_number VARCHAR(20)        -- This column CAN be blank (NULL allowed).
);

*(If a query executes INSERT INTO users (phonenumber) VALUES ('555-5555'), PostgreSQL will instantly crash and reject the query because email and passwordhash were not provided).*

4. The DEFAULT Constraint #

CREATE TABLE users (
    user_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,             -- Defaults to True!
    created_at TIMESTAMPTZ DEFAULT NOW()        -- Automatically stamps the exact time!
);

*(Now, if you run INSERT INTO users (username) VALUES ('john');, the database automatically generates the ID, sets him to active, and stamps the exact UTC time).*

5. The UNIQUE Constraint (A Quick Review) #

6. The Powerful CHECK Constraint #

CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0), -- Price MUST be greater than 0!
    discount_percent INT DEFAULT 0 CHECK (discount_percent BETWEEN 0 AND 100)
);

*(If a buggy backend script executes UPDATE products SET price = -50, PostgreSQL evaluates the CHECK (price > 0) constraint, sees it is false, and aggressively blocks the update).*

7. Adding Constraints to Existing Tables #

-- Oops, we forgot to ensure salaries are positive! Let's add the rule.
ALTER TABLE employees 
ADD CONSTRAINT chk_positive_salary CHECK (salary > 0);

-- Let's make the department column mandatory
ALTER TABLE employees 
ALTER COLUMN department SET NOT NULL;

8. Mini Project: The Bulletproof Employee Table #

CREATE TABLE employees (
    emp_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INT CHECK (age >= 18), -- Must be a legal adult
    salary NUMERIC(10, 2) NOT NULL CHECK (salary >= 30000.00), -- Minimum wage rule
    department VARCHAR(50) DEFAULT 'Unassigned',
    hire_date DATE DEFAULT CURRENT_DATE
);

9. Common Mistakes #

• Confusing Application Validation with Database Constraints: You should *always* validate emails and numbers in the frontend (React) and the backend (Node.js/PHP). However, you must *never* rely on them exclusively. Forms get bypassed. APIs get hit directly. The Database Constraint is the final, unbreakable wall.

• Applying NOT NULL to everything: Beginners often apply NOT NULL to every single column. This causes massive headaches later when forms are updated or optional fields are introduced. Only use NOT NULL on data that is mathematically critical to the application's survival.

10. Best Practices #

• Name your CHECK constraints: By default, if you just write CHECK (age > 18), PostgreSQL generates a random, ugly name for the constraint (like employeesagecheck). If the constraint fails, the error log just prints the ugly name. It is better to explicitly name them: CONSTRAINT mustbeadult CHECK (age > 18).

11. Exercises #

1. 1. Write the DDL to create a role column that defaults to the string 'Subscriber' if no data is provided.

1. 2. Write an ALTER TABLE command to add a UNIQUE constraint to an existing phone_number column.

12. SQL Challenges #

CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    task_name VARCHAR(200) NOT NULL,
    status VARCHAR(20) DEFAULT 'Pending' 
        CHECK (status IN ('Pending', 'In Progress', 'Completed'))
);

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: In a modern web stack, data is often validated in the frontend (e.g., React) and in the backend controller (e.g., Laravel/Django). Why is it still an absolute architectural requirement to duplicate validation logic using Database Constraints?

• Q: Explain the difference between UNIQUE and PRIMARY KEY. Can a table have multiple UNIQUE columns?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #