NOT NULL & UNIQUE Constraints: Ensuring Data Quality

While primary and foreign keys enforce relationships, NOT NULL and UNIQUE constraints ensure data quality at the column level. NOT NULL guarantees that required data is always present, while UNIQUE prevents duplicate values. Together, they form a crucial layer of data validation that catches errors before they corrupt your database. In this lesson, you'll learn when to use each constraint, how they affect inserts and updates, and how they improve overall data integrity.

📚 What You'll Learn

• What NOT NULL constraint does and why it matters
• What UNIQUE constraint does and when to use it
• The difference between NOT NULL and UNIQUE
• How these constraints affect INSERT operations
• How these constraints affect UPDATE operations
• How to combine NOT NULL and UNIQUE
• When to use UNIQUE vs. making something a primary key
• How to add and remove constraints
• Best practices for data validation at the database level

1. NOT NULL: Ensuring Required Data

What is NULL?

NULL is a special marker in SQL that means "no value" or "unknown." It's different from an empty string ('') or zero (0).

Example: With and Without NOT NULL

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),  -- No NOT NULL
    email VARCHAR(255)  -- No NOT NULL
);

-- This succeeds, but creates incomplete data! 😱
INSERT INTO students (student_id) VALUES (100234);

-- Result: student with no name or email
SELECT * FROM students;
-- +------------+------+-------+
-- | student_id | name | email |
-- +------------+------+-------+
-- |     100234 | NULL | NULL  |
-- +------------+------+-------+

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,   -- Required!
    email VARCHAR(255) NOT NULL   -- Required!
);

-- This fails - database protects you ✓
INSERT INTO students (student_id) VALUES (100234);
-- ERROR: Column 'name' cannot be null

-- This succeeds
INSERT INTO students (name, email) 
VALUES ('Alice Johnson', 'alice@uni.edu');

When to Use NOT NULL

2. UNIQUE: Preventing Duplicates

Example: Preventing Duplicate Emails

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL  -- No UNIQUE!
);

-- Both succeed, creating duplicate emails 😱
INSERT INTO users (username, email) 
VALUES ('alice', 'alice@example.com');

INSERT INTO users (username, email) 
VALUES ('alice2', 'alice@example.com');  -- Same email!

SELECT * FROM users;
-- +---------+----------+--------------------+
-- | user_id | username | email              |
-- +---------+----------+--------------------+
-- |       1 | alice    | alice@example.com  |
-- |       2 | alice2   | alice@example.com  |  ← Duplicate!
-- +---------+----------+--------------------+

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,  -- UNIQUE!
    email VARCHAR(255) NOT NULL UNIQUE      -- UNIQUE!
);

-- First insert succeeds
INSERT INTO users (username, email) 
VALUES ('alice', 'alice@example.com');

-- Second insert FAILS - database protects you ✓
INSERT INTO users (username, email) 
VALUES ('alice2', 'alice@example.com');
-- ERROR: Duplicate entry 'alice@example.com' for key 'email'

UNIQUE vs. Primary Key

3. Combining NOT NULL and UNIQUE

You can (and often should) use both constraints together:

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,  -- Required AND unique
    email VARCHAR(255) NOT NULL UNIQUE,     -- Required AND unique
    bio TEXT                                -- Optional, can be NULL
);

This ensures that:

• ✓ Every user must have a username (NOT NULL)
• ✓ No two users can have the same username (UNIQUE)
• ✓ Every user must have an email (NOT NULL)
• ✓ No two users can have the same email (UNIQUE)
• ✓ bio is optional and can be different or NULL

Testing the Constraints

-- ✓ Success: all required fields, all unique
INSERT INTO users (username, email) 
VALUES ('alice', 'alice@example.com');

-- ❌ Fails: missing required field (email)
INSERT INTO users (username) VALUES ('bob');
-- ERROR: Column 'email' cannot be null

-- ❌ Fails: duplicate username
INSERT INTO users (username, email) 
VALUES ('alice', 'different@example.com');
-- ERROR: Duplicate entry 'alice' for key 'username'

-- ❌ Fails: duplicate email
INSERT INTO users (username, email) 
VALUES ('bob', 'alice@example.com');
-- ERROR: Duplicate entry 'alice@example.com' for key 'email'

-- ✓ Success: all fields valid and unique
INSERT INTO users (username, email, bio) 
VALUES ('bob', 'bob@example.com', 'Software developer');

4. How Constraints Affect INSERT and UPDATE

INSERT Operations

UPDATE Operations

-- Assume users table with username UNIQUE NOT NULL

-- ❌ Fails: can't set to NULL
UPDATE users 
SET username = NULL 
WHERE user_id = 1;
-- ERROR: Column 'username' cannot be null

-- ❌ Fails: can't set to existing value
UPDATE users 
SET username = 'bob'  -- bob already exists
WHERE user_id = 1;
-- ERROR: Duplicate entry 'bob' for key 'username'

-- ✓ Success: new unique value
UPDATE users 
SET username = 'alice_new' 
WHERE user_id = 1;

5. Multiple UNIQUE Columns

A table can have multiple UNIQUE constraints:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,           -- Must be unique
    ssn VARCHAR(11) UNIQUE,                       -- Optional but unique if provided
    badge_number VARCHAR(20) NOT NULL UNIQUE,     -- Must be unique
    phone VARCHAR(20)                             -- Can be duplicate
);

-- Each UNIQUE column is enforced independently
-- email, ssn, and badge_number must all be unique
-- But phone can be shared (no UNIQUE constraint)

Composite UNIQUE Constraint

You can also make a combination of columns unique:

CREATE TABLE course_sections (
    section_id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(20) NOT NULL,
    semester VARCHAR(20) NOT NULL,
    section_number INT NOT NULL,
    instructor VARCHAR(255),
    -- Combination must be unique (but individual values can repeat)
    UNIQUE (course_code, semester, section_number)
);

-- ✓ Allowed: CS101 can have multiple sections in different semesters
INSERT INTO course_sections (course_code, semester, section_number)
VALUES ('CS101', 'Fall2024', 1);

INSERT INTO course_sections (course_code, semester, section_number)
VALUES ('CS101', 'Spring2025', 1);

-- ❌ Fails: CS101 Fall2024 section 1 already exists
INSERT INTO course_sections (course_code, semester, section_number)
VALUES ('CS101', 'Fall2024', 1);
-- ERROR: Duplicate entry 'CS101-Fall2024-1' for key

6. Adding and Removing Constraints

Adding NOT NULL

-- Add NOT NULL to existing column
ALTER TABLE students
MODIFY email VARCHAR(255) NOT NULL;

-- WARNING: This fails if any existing rows have NULL email!

Adding UNIQUE

-- Add UNIQUE to existing column
ALTER TABLE students
ADD UNIQUE (email);

-- Or with named constraint
ALTER TABLE students
ADD CONSTRAINT uq_student_email UNIQUE (email);

-- WARNING: This fails if any duplicate emails exist!

Removing Constraints

-- Remove UNIQUE constraint (by name)
ALTER TABLE students
DROP INDEX uq_student_email;

-- Remove NOT NULL (make column nullable)
ALTER TABLE students
MODIFY email VARCHAR(255) NULL;

7. 🎯 Practice Exercises

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(50) NOT NULL UNIQUE,
    price DECIMAL(10, 2) NOT NULL,
    description TEXT
);

-- Test inserts
-- ✓ Success
INSERT INTO products (name, sku, price)
VALUES ('Laptop', 'LAP-001', 999.99);

-- ❌ Fails: missing required field (sku)
INSERT INTO products (name, price)
VALUES ('Mouse', 29.99);
-- ERROR: Column 'sku' cannot be null

-- ❌ Fails: duplicate SKU
INSERT INTO products (name, sku, price)
VALUES ('Keyboard', 'LAP-001', 79.99);
-- ERROR: Duplicate entry 'LAP-001' for key 'sku'

-- ✓ Success: description is optional
INSERT INTO products (name, sku, price, description)
VALUES ('Monitor', 'MON-001', 299.99, '27-inch 4K display');

-- ❌ Problematic design
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255),
    phone VARCHAR(20),
    first_name VARCHAR(100),
    last_name VARCHAR(100)
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,  -- Required and must be unique
    phone VARCHAR(20),                    -- Optional (not everyone has phone)
    first_name VARCHAR(100) NOT NULL,     -- Required
    last_name VARCHAR(100) NOT NULL       -- Required
);

-- Now database enforces data quality:
-- - Every customer MUST have email (and it's unique)
-- - Every customer MUST have first and last name
-- - Phone is optional

CREATE TABLE student_courses (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL DEFAULT CURRENT_DATE,
    
    -- Foreign keys
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    
    -- Composite UNIQUE: prevent duplicate enrollments
    UNIQUE (student_id, course_id)
);

-- ✓ Student 100 enrolls in Course 1
INSERT INTO student_courses (student_id, course_id)
VALUES (100, 1);

-- ✓ Student 200 enrolls in Course 1 (same course, different student - OK)
INSERT INTO student_courses (student_id, course_id)
VALUES (200, 1);

-- ✓ Student 100 enrolls in Course 2 (same student, different course - OK)
INSERT INTO student_courses (student_id, course_id)
VALUES (100, 2);

-- ❌ Student 100 tries to enroll in Course 1 again (duplicate!)
INSERT INTO student_courses (student_id, course_id)
VALUES (100, 1);
-- ERROR: Duplicate entry '100-1' for key

CREATE TABLE social_users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,      -- Required and unique
    email VARCHAR(255) NOT NULL UNIQUE,        -- Required and unique
    phone VARCHAR(20) UNIQUE,                  -- Optional but unique if provided
    display_name VARCHAR(100),                 -- Optional, can be duplicate
    bio TEXT,                                  -- Optional
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Test scenarios

-- ✓ Success: All required fields provided
INSERT INTO social_users (username, email, display_name)
VALUES ('alice123', 'alice@example.com', 'Alice Johnson');

-- ✓ Success: Phone provided and unique
INSERT INTO social_users (username, email, phone)
VALUES ('bob456', 'bob@example.com', '555-1234');

-- ✓ Success: Same display name is allowed
INSERT INTO social_users (username, email, display_name)
VALUES ('carol789', 'carol@example.com', 'Alice Johnson');

-- ❌ Fails: Duplicate username
INSERT INTO social_users (username, email)
VALUES ('alice123', 'different@example.com');
-- ERROR: Duplicate entry 'alice123' for key 'username'

-- ❌ Fails: Duplicate phone
INSERT INTO social_users (username, email, phone)
VALUES ('dave999', 'dave@example.com', '555-1234');
-- ERROR: Duplicate entry '555-1234' for key 'phone'

-- ✓ Success: Multiple users with NULL phone (NULL != NULL in UNIQUE)
INSERT INTO social_users (username, email)
VALUES ('eve111', 'eve@example.com');

INSERT INTO social_users (username, email)
VALUES ('frank222', 'frank@example.com');

📝 Key Takeaways

• NOT NULL ensures required data is always present
• UNIQUE prevents duplicate values in a column
• Use NOT NULL when data is absolutely required
• Use UNIQUE when values must be distinct (emails, usernames, SKUs)
• Can combine NOT NULL and UNIQUE for required, unique fields
• Primary keys are automatically NOT NULL and UNIQUE
• UNIQUE columns can be NULL (and multiple NULLs are allowed)
• Tables can have multiple UNIQUE columns
• Composite UNIQUE constraints enforce uniqueness on combinations
• Constraints are enforced on both INSERT and UPDATE operations
• Adding constraints to existing tables fails if data violates constraints
• Database-level validation is more reliable than application-level validation