Skip to content

CHECK & DEFAULT Constraints ⚙️

Prerequisites: CREATE TABLE

Mentor's Note: Constraints aren't just for uniqueness. They can also act as "Smart Assistants." DEFAULT saves you time by auto-filling data, and CHECK ensures your data makes logical sense (like no negative salaries!). 💡

🌟 The Scenario: The Online Store 🛒

Imagine you are managing an inventory system.

  1. DEFAULT: If you don't specify a shipping city, the computer assumes it's "Surat". 📍
  2. CHECK: The system refuses to save a product if the Stock Count is less than zero. (You can't have negative boxes!). 🚫

💻 1. The DEFAULT Constraint

Automatically fills a column with a value if the INSERT statement leaves it blank.

Syntax

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE DEFAULT SYSDATE, -- 📅 Auto-fill today's date
    city VARCHAR(50) DEFAULT 'Surat' -- 📍 Default local city
);

How it works

-- Inserting only the ID
INSERT INTO orders (order_id) VALUES (501);
-- Result: [501, 2026-02-15, 'Surat']

💻 2. The CHECK Constraint

Forces a logical rule on the data. If the rule is violated, the row is rejected.

Syntax

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    salary DECIMAL(10, 2) CHECK (salary >= 15000), -- 💰 Min wage rule
    age INT CHECK (age BETWEEN 18 AND 65)          -- 🎂 Working age rule
);

Adding to an Existing Table

ALTER TABLE employees ADD CONSTRAINT chk_bonus CHECK (bonus < salary);

🎨 Visual Logic: The Quality Filter

graph LR
    A[Value: -10] --> B{CHECK stock >= 0}
    B -- "False" --> C[Reject Entry ❌]

    D[No City Provided] --> E{Has DEFAULT?}
    E -- "Yes" --> F[Insert 'Surat' ✅]

💡 Interview Tip 👔

"Can a CHECK constraint reference columns in other tables? No. CHECK can only validate data within its own row. If you need to check data in other tables, you must use a Trigger."

📈 Learning Path