Constraints in SQL

🔐 What Are Constraints in SQL?

Constraints are rules applied to table columns to control the type of data that can go into a table. They protect your data and make sure it stays accurate and consistent.

---

✅ Types of SQL Constraints

---

1️⃣ NOT NULL

Means the column must have a value.

CREATE TABLE Students (
  ID INT NOT NULL,
  Name VARCHAR(50) NOT NULL
);

👉 You cannot insert a student without ID or Name.

---

2️⃣ UNIQUE

Makes sure all values in that column are different.

CREATE TABLE Users (
  Email VARCHAR(100) UNIQUE
);

👉 No two users can have the same email.

---

3️⃣ PRIMARY KEY

• Combines NOT NULL and UNIQUE.

• Only one primary key per table.

• Used to identify rows uniquely.

CREATE TABLE Employees (
  EmpID INT PRIMARY KEY,
  Name VARCHAR(50)
);

👉 Each EmpID must be unique and not null.

---

4️⃣ FOREIGN KEY

Used to link one table to another.

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

👉 CustomerID in Orders must match an existing CustomerID in Customers table.

---

5️⃣ CHECK

Makes sure the value follows a certain rule.

CREATE TABLE Products (
  Price INT CHECK (Price > 0)
);

👉 Price must always be greater than 0.

---

6️⃣ DEFAULT

Sets a value automatically if not provided.

CREATE TABLE Accounts (
  Balance INT DEFAULT 0
);

👉 If no balance is given, it will be 0 by default.

---

✅ Summary Table:

🎓 Example Table: Students

🎯 Goal:

We want to create a table that:

• Has a unique ID for every student

• Requires name and age

• Doesn't allow duplicate roll numbers

• Ensures age is at least 5

• Assigns a default country if not given

• Links to another table for class info

---

✅ Step-by-step SQL Code with Constraints:

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,                -- Each student must have a unique ID (not null + unique)
  RollNumber INT UNIQUE,                    -- No two students can have same roll number
  Name VARCHAR(50) NOT NULL,                -- Name is required
  Age INT CHECK (Age >= 5),                 -- Age must be 5 or more
  Country VARCHAR(50) DEFAULT 'India',      -- If country not given, default is India
  ClassID INT,                              -- ClassID will link to another table
  FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)  -- Must match a ClassID in Classes table
);

---

🔍 Now Let's Understand Each Constraint:

---

📄 Related Table: Classes

CREATE TABLE Classes (
  ClassID INT PRIMARY KEY,
  ClassName VARCHAR(30)
);