SQL Constraints: The Rules That Keep Your Data Safe 🛡️

Imagine a school with no rules. Kids would run everywhere, eat candy for lunch, and nobody would know who belongs in which class. Chaos!

Databases need rules too. These rules are called constraints. They keep your data organized, clean, and trustworthy.

Let’s explore each constraint like a story!

🔑 Primary Key Constraint

The One and Only ID Badge

Think of a Primary Key like your school ID number. Every student has a unique number. No two students share the same ID. And you can’t be a student without one!

What it does:

• Makes sure every row is unique
• Never allows empty values (NULL)
• Only one primary key per table

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(100),
  grade INT
);

Real Life Example:

• Your passport number = Primary Key
• Your phone’s serial number = Primary Key
• Each YouTube video’s unique code = Primary Key

💡 Remember: One table, one Primary Key. It’s the boss of identities!

🔗 Foreign Key Constraint

The Friendship Bracelet

Imagine two friends with matching bracelets. The bracelet proves they’re connected!

A Foreign Key is like that bracelet. It connects one table to another.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
);

What it does:

• Links two tables together
• Makes sure you can’t add fake connections
• If a customer doesn’t exist, you can’t create an order for them!

graph TD
    A[customers table] -->|customer_id| B[orders table]
    A -->|Primary Key| C[customer_id = 1]
    B -->|Foreign Key| D[customer_id = 1]

💡 Remember: Foreign Keys point to Primary Keys in other tables. They’re the glue!

🎹 Composite Primary Keys

Two Keys Are Better Than One

Sometimes, one key isn’t enough. Imagine a music playlist:

• Song name alone? Many songs have the same name!
• Artist name alone? Artists make many songs!
• Song + Artist together? Now that’s unique!

CREATE TABLE playlist_songs (
  song_name VARCHAR(100),
  artist_name VARCHAR(100),
  added_date DATE,
  PRIMARY KEY (song_name, artist_name)
);

When to use:

• When one column can’t be unique alone
• When the combination makes sense together
• Like coordinates: (3, 5) is unique, but 3 and 5 alone aren’t

💡 Remember: Composite = Combined. Two or more columns working as a team!

🏷️ Surrogate vs Natural Keys

Made-Up Names vs Real Names

Natural Key: Uses real data that already exists

• Example: Email address, Social Security Number

Surrogate Key: A made-up ID just for the database

• Example: Auto-generated numbers like 1, 2, 3…

-- NATURAL KEY (using email)
CREATE TABLE users_natural (
  email VARCHAR(255) PRIMARY KEY,
  name VARCHAR(100)
);

-- SURROGATE KEY (made-up ID)
CREATE TABLE users_surrogate (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255),
  name VARCHAR(100)
);

💡 Most databases use Surrogate Keys because they’re simple, short, and never change!

🚫 NOT NULL Constraint

The “You Must Fill This Out” Rule

Remember forms that say “Required Field”? That’s NOT NULL!

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  nickname VARCHAR(50)  -- This CAN be empty
);

What happens without NOT NULL:

Yikes! A customer with no name or email? Useless data!

What NOT NULL prevents:

• Empty important fields
• Confusing missing data
• Broken relationships

💡 Remember: NOT NULL = REQUIRED. No blanks allowed!

⭐ UNIQUE Constraint

No Copycats Allowed

UNIQUE is like saying “Only one person can have this username.”

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(255) UNIQUE,
  display_name VARCHAR(100)
);

Primary Key vs UNIQUE:

Real Life:

• Email addresses = UNIQUE (no two users same email)
• Phone numbers = UNIQUE
• License plates = UNIQUE

💡 Remember: UNIQUE allows one NULL, Primary Key allows none!

🎯 DEFAULT Constraint

The Auto-Fill Helper

Ever noticed how forms pre-fill today’s date? That’s DEFAULT!

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'pending',
  created_at DATE DEFAULT CURRENT_DATE,
  quantity INT DEFAULT 1
);

Now when you add an order:

INSERT INTO orders (order_id)
VALUES (1);

The row becomes:

Why it’s helpful:

• Less typing
• Fewer mistakes
• Consistent data

💡 Remember: DEFAULT = “If you don’t tell me, I’ll use this!”

✅ CHECK Constraint

The Bouncer at the Door

CHECK is like a bouncer. It checks if data follows the rules before letting it in!

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2) CHECK (price > 0),
  rating INT CHECK (rating BETWEEN 1 AND 5),
  quantity INT CHECK (quantity >= 0)
);

What CHECK prevents:

❌ Price = -50 (Can’t be negative!) ❌ Rating = 10 (Must be 1-5!) ❌ Quantity = -3 (Can’t have negative items!)

More Examples:

-- Age must be 18 or older
age INT CHECK (age >= 18)

-- Status must be specific values
status VARCHAR(20)
  CHECK (status IN ('active','inactive'))

-- Email must contain @
email VARCHAR(255)
  CHECK (email LIKE '%@%')

💡 Remember: CHECK = Custom rules. You decide what’s allowed!

🎮 All Constraints Together

Here’s a complete table using ALL constraints:

CREATE TABLE employees (
  -- Surrogate Primary Key
  emp_id INT AUTO_INCREMENT PRIMARY KEY,

  -- NOT NULL (required fields)
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,

  -- UNIQUE (no duplicates)
  email VARCHAR(255) UNIQUE NOT NULL,

  -- DEFAULT (auto-fill)
  hire_date DATE DEFAULT CURRENT_DATE,
  status VARCHAR(20) DEFAULT 'active',

  -- CHECK (custom rules)
  salary DECIMAL(10,2) CHECK (salary > 0),
  age INT CHECK (age >= 18),

  -- Foreign Key (connection)
  department_id INT,
  FOREIGN KEY (department_id)
    REFERENCES departments(dept_id)
);

🧠 Quick Memory Guide

graph TD
    A[SQL Constraints] --> B[PRIMARY KEY]
    A --> C[FOREIGN KEY]
    A --> D[COMPOSITE KEY]
    A --> E[NOT NULL]
    A --> F[UNIQUE]
    A --> G[DEFAULT]
    A --> H[CHECK]

    B --> B1[Unique ID for each row]
    C --> C1[Links tables together]
    D --> D1[Multiple columns = 1 key]
    E --> E1[Required field]
    F --> F1[No duplicates]
    G --> G1[Auto-fill value]
    H --> H1[Custom rules]

🌟 The Big Picture

You did it! 🎉

You now understand how constraints keep databases clean and trustworthy. They’re like the rules of a well-organized school:

• Everyone has an ID (Primary Key)
• Friends are tracked (Foreign Key)
• Important fields can’t be empty (NOT NULL)
• No copycats (UNIQUE)
• Smart defaults save time (DEFAULT)
• Custom rules keep things right (CHECK)

Your data is now safe and sound! 🛡️