SQL Database Constraints | NOT NULL, UNIQUE, CHECK

# CHAPTER 13

Constraints and Data Integrity #

1. Introduction #

2. Learning Objectives #

• Define the architectural purpose of Constraints.

• Prevent missing data using NOT NULL.

• Prevent duplicate data using the UNIQUE constraint.

• Set fallback data using DEFAULT.

• Enforce strict custom business rules using the CHECK constraint.

3. The NOT NULL Constraint #

CREATE TABLE users (
    id INT PRIMARY KEY,
    -- If an insert lacks a name or email, SQL throws a fatal error!
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL 
);

4. The UNIQUE Constraint #

CREATE TABLE users (
    id INT PRIMARY KEY,
    -- Cannot be empty, and mathematically cannot be a duplicate!
    email VARCHAR(150) NOT NULL UNIQUE 
);

5. The DEFAULT Constraint #

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,
    -- Automatically FALSE unless the backend explicitly specifies otherwise
    is_verified BOOLEAN DEFAULT FALSE 
);

6. The CHECK Constraint (Custom Business Logic) #

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    
    -- The database will reject any INSERT or UPDATE where price is < 0!
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0.00)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    age INT NOT NULL CHECK (age >= 18)
);

7. Mini Project: Secure User Database Schema #

CREATE TABLE secure_users (
    -- The Mathematical Fingerprint
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Mandatory, Unique identifiers
    username VARCHAR(30) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    
    -- Optional data (No constraints)
    bio TEXT,
    
    -- Mandatory data with a secure fallback
    account_status VARCHAR(20) NOT NULL DEFAULT 'Pending',
    
    -- Ensuring usernames are at least 4 characters long!
    CONSTRAINT chk_username_length CHECK (LENGTH(username) >= 4),
    
    -- Auto-timestamping the registration
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

8. Naming Your Constraints #

9. Common Mistakes #

• Confusing UNIQUE with PRIMARY KEY: Beginners often ask, "Why not just make the Email the Primary Key since it is UNIQUE?" Primary Keys are used heavily in JOIN queries. Comparing integers (id = 5) is mathematically 100x faster than comparing strings (email = 'john@test.com'). Always use an integer Primary Key, and use a UNIQUE constraint for the string.

10. Best Practices #

• Double Validation: A common question is: "Should I validate the age in the JavaScript frontend, the Python backend, or the Database CHECK constraint?" The answer is All Three. The frontend for user experience, the backend for primary logic, and the Database as the final, absolute guarantee of Data Integrity.

11. Exercises #

1. 1. What constraint forces the database to automatically reject an INSERT command if the backend developer forgets to provide an email address?

1. 2. What constraint allows you to enforce custom logical rules, such as ensuring a salary is greater than $30,000?

12. Database Design Challenges #

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the philosophical defense-in-depth strategy of utilizing Database Constraints (like NOT NULL and CHECK) even if the application backend (e.g., Express.js or Django) already has rigorous data validation middleware.

• Q: Contrast the operational differences between the PRIMARY KEY constraint and the UNIQUE constraint. Can a single table have multiple UNIQUE columns? Can it have multiple PRIMARY KEY columns?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #