Skip to content

SQL Constraints: The Data Rules ⚖️

Prerequisites: Create Table Basics

Mentor's Note: Constraints are the "Guardrails" of your database. Without them, you could have a student with no name, or a negative salary. Constraints ensure your data stays high-quality and trustworthy. 💡

🌟 The Scenario: The School Registry 🏫

Imagine you are the principal of a school.

  1. NOT NULL: Every student must have a name. 👤
  2. UNIQUE: No two students can have the same Email ID. 📧
  3. PRIMARY KEY: Every student must have a unique Roll Number to identify them. 🆔
  4. CHECK: A student's age must be at least 5 years old. 🎂
  5. DEFAULT: If no city is provided, assume they are from "Surat". 📍

🎨 Visual Logic: The Constraint Guard

graph TD
    A[Data Entry 📥] --> B{Constraints?}
    B -- "Fail ❌" --> C[Reject Action 🚫]
    B -- "Pass ✅" --> D[Save to Table 🗄️]

    subgraph "Rule Set"
    B1[NOT NULL]
    B2[UNIQUE]
    B3[PRIMARY KEY]
    B4[CHECK]
    end

💻 Common Constraints (DDL Syntax)

CREATE TABLE STUDENTS (
    roll_no   NUMBER(5) PRIMARY KEY, -- 🆔 Unique ID
    name      VARCHAR2(50) NOT NULL, -- 👤 Mandatory
    email     VARCHAR2(100) UNIQUE,  -- 📧 No Duplicates
    age       NUMBER(3) CHECK (age >= 5), -- 🎂 Age Rule
    city      VARCHAR2(50) DEFAULT 'Surat' -- 📍 Auto-fill
);

CREATE TABLE STUDENTS (
    roll_no   INT PRIMARY KEY AUTO_INCREMENT,
    name      VARCHAR2(50) NOT NULL,
    email     VARCHAR2(100) UNIQUE,
    age       INT CHECK (age >= 5),
    city      VARCHAR2(50) DEFAULT 'Surat'
);

📖 Deep Dive: Primary vs Foreign Key

1. Primary Key (The Anchor ⚓)

  • Unique identify for every row.
  • Cannot be NULL.
  • Only one per table.

2. Foreign Key (The Bridge 🌉)

  • Links one table to another.
  • Points to the Primary Key of another table.
  • Maintains Referential Integrity.
-- 🌉 Linking Employees to Departments
CREATE TABLE EMPLOYEE (
    emp_id NUMBER PRIMARY KEY,
    dept_id NUMBER REFERENCES DEPARTMENT(dept_id) -- 🔗 Foreign Key
);

📊 Comparison Table

Constraint Can be NULL? Unique? Limit per Table
PRIMARY KEY ❌ No ✅ Yes Only 1
UNIQUE ✅ Yes ✅ Yes Multiple
NOT NULL ❌ No ❌ No Multiple

💡 Interview Tip 👔

"Interviewers often ask: 'Can a UNIQUE constraint have a NULL value?' The answer is YES! In most databases, you can have one (or sometimes multiple) NULL values in a UNIQUE column, because NULL is not equal to NULL."

📈 Learning Path

💡 Pro Tip: "Bad data is worse than no data. Use constraints early so you don't have to clean up a mess later."