Constraints and Data Integrity

# CHAPTER 19

Constraints and Data Integrity #

1. Introduction #

2. Learning Objectives #

• Explain the purpose of Data Integrity constraints.

• Prevent blank data using NOT NULL.

• Prevent duplicate data using UNIQUE.

• Provide fallback data using DEFAULT.

• Enforce business logic using CHECK constraints.

3. The NOT NULL Constraint #

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,    -- This MUST be provided!
    bio TEXT                          -- This is allowed to be empty (NULL).
);

*If a PHP script attempts INSERT INTO users (bio) VALUES ('Hello');, MySQL will immediately crash the script with an error because username was omitted.*

4. The UNIQUE Constraint #

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,  -- Cannot be blank, Cannot be duplicated!
    ssn VARCHAR(11) UNIQUE               -- Cannot be duplicated
);

5. The DEFAULT Constraint #

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'Pending',   -- Automatically sets to 'Pending'
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP -- Automatically grabs the current server time!
);

*If you run INSERT INTO orders (productname) VALUES ('Laptop');, MySQL will flawlessly fill in the status and orderdate without you typing them!*

6. The CHECK Constraint (Business Logic) #

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    discount_percent INT DEFAULT 0,
    
    -- RULE 1: Price can never be negative
    CONSTRAINT chk_price CHECK (price >= 0),
    
    -- RULE 2: Discount must be between 0 and 100
    CONSTRAINT chk_discount CHECK (discount_percent >= 0 AND discount_percent <= 100)
);

*If a hacker manipulates a form to submit a price of -50.00, the PHP might miss it, but the MySQL CHECK constraint will instantly block it and protect your finances!*

7. Adding Constraints to Existing Tables #

-- Add a DEFAULT rule to an existing column
ALTER TABLE users 
ALTER COLUMN role SET DEFAULT 'Subscriber';

-- Add a UNIQUE constraint
ALTER TABLE users 
ADD UNIQUE (username);

8. Common Mistakes #

• Trusting Frontend Validation Only: Beginners often put required tags on their HTML forms and assume the data is safe. HTML can be bypassed in 3 seconds using browser dev tools. Backend PHP validation is better, but bugs happen. Database Constraints are the only guarantee of data integrity.

• Overusing NOT NULL: Don't put NOT NULL on columns that genuinely might not be known yet (like cancellationdate on a fresh order). Only use it for absolutely critical required data.

• Naming Constraints: When using CHECK, always name your constraints using CONSTRAINT name CHECK (...). If you don't provide a name, MySQL generates a random gibberish name (like chk1a2b). If the constraint blocks an insertion in production, the error log will say "Constraint chk1a2b failed", and you will have no idea what rule was broken!

1. 2. How does DEFAULT CURRENTTIMESTAMP simplify your backend application code?

11. MCQ Quiz with Answers #

12. Interview Questions #

• Q: Explain the difference between a PRIMARY KEY constraint and a UNIQUE constraint. Can a table contain multiple of both?

• Q: Describe a business scenario where utilizing a DEFAULT constraint significantly cleans up and simplifies the backend INSERT logic.

13. FAQs #

14. Summary #

15. Next Chapter Recommendation #