SQL Constraints Explained | Primary Key, UNIQUE, NOT NULL

# CHAPTER 14

Primary Keys and Constraints #

1. Introduction #

2. Learning Objectives #

• Understand the definition and critical necessity of Constraints.

• Implement the PRIMARY KEY to uniquely identify rows.

• Utilize AUTO_INCREMENT / SERIAL to generate IDs mathematically.

• Prevent missing data using NOT NULL.

• Prevent duplicate data using the UNIQUE constraint.

• Set fallback data using DEFAULT.

3. The PRIMARY KEY (The Fingerprint) #

A Primary Key has two strict rules:

1. 1. It must be absolutely UNIQUE (No two rows can have the same key).

1. 2. It cannot be NULL (Every row must possess a key).

CREATE TABLE users (
    -- Designate the 'id' column as the Primary Key
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

4. Auto-Incrementing the Primary Key #

• MySQL: AUTO_INCREMENT

• PostgreSQL: SERIAL

-- MySQL Syntax for a perfect ID column!
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

*(Now, when you insert data, you simply ignore the ID column. SQL handles the math automatically: 1, 2, 3...)*

5. The NOT NULL Constraint #

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

6. The UNIQUE Constraint #

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

7. The DEFAULT Constraint #

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    is_verified BOOLEAN DEFAULT FALSE -- Automatically FALSE unless specified otherwise!
);

8. Mini Project: The Bulletproof User 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',
    
    -- Auto-timestamping the registration
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

9. Common Mistakes #

• Using Emails or Names as Primary Keys: A beginner might say "Emails are unique, let's make email the Primary Key!" This is a terrible architectural decision. If the user changes their email address, the Primary Key changes, breaking all relationships to other tables. Primary Keys should be meaningless, auto-incrementing Integers that never change.

10. Best Practices #

• The Check Constraint: Some databases (like PostgreSQL and modern MySQL) support CHECK. You can add age INT CHECK (age >= 18) to physically block minors from being inserted into the database!

11. Exercises #

1. 1. What are the two strict mathematical rules that define a PRIMARY KEY?

1. 2. What constraint tells SQL to automatically insert a specific value if the developer forgets to provide one during an INSERT?

12. SQL Challenges #

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    sku_code VARCHAR(50) NOT NULL UNIQUE,
    price DECIMAL(10, 2) DEFAULT 0.00
);

13. MCQ Quiz with Answers #

14. Interview Questions #

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

• Q: A junior developer manually calculates the id for new user inserts by querying SELECT MAX(id) + 1. Explain why this is catastrophically dangerous in a highly concurrent web application, and explain how the AUTOINCREMENT/SERIAL features solve it.

16. Summary #

17. Next Chapter Recommendation #