NOT NULL & UNIQUE Constraints 🛡️¶

Mentor's Note: Imagine a registration form where someone forgets to write their name, or two people use the same username. These constraints are the "Validation Rules" that prevent these mistakes from ever reaching your database. 💡

Imagine you are building a new social media app.

NOT NULL: Every user must provide a name. You can't have a "Ghost" user with no identity. 👤
UNIQUE: No two users can have the same Username or Email. If 'VD_Surat' is taken, nobody else can use it. 💎

💻 1. The NOT NULL Constraint¶

By default, a column can hold NULL values. NOT NULL forces a column to always contain a value.

Syntax (During Table Creation)¶

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL, -- 👤 Must have a name
    phone VARCHAR(15)                -- 📱 Optional (can be NULL)
);

Adding to an Existing Table¶

-- Scenario: Make Email mandatory after the table is built
-- ⚠️ Only works if existing rows have no NULL emails!
ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL;

💻 2. The UNIQUE Constraint¶

Ensures that all values in a column are different.

Syntax¶

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE, -- 💎 No two users same username
    email VARCHAR(100)
);

Multiple Column Uniqueness¶

You can ensure a combination of columns is unique.

-- Scenario: A student can only enroll once in a specific course
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    UNIQUE (student_id, course_id)
);

🎨 Visual Logic: The Gatekeeper¶

graph TD
    A[New User Entry] --> B{Email Provided?}
    B -- No --> C[NOT NULL Error ❌]
    B -- Yes --> D{Email Unique?}
    D -- No --> E[UNIQUE Error ❌]
    D -- Yes --> F[Welcome to the App! ✅]

💡 Interview Tip 👔¶

"Can a UNIQUE column have a NULL value? Yes! In most databases, multiple NULLs are allowed in a UNIQUE column because NULL is not equal to NULL. However, in some systems (like SQL Server), only one NULL is allowed."