@Codingdidi

Day 23: Constraints in SQL 🚀

Welcome to Day 23 of your SQL journey! Today, we will cover SQL Constraints in depth.

🔹 What are Constraints in SQL?
Constraints control the rules for the data stored in a database table. They ensure accuracy, integrity, and consistency of the data.

👉 Constraints help in preventing invalid data from being inserted.

📌 Today, we will learn about:
✔️ PRIMARY KEY
✔️ FOREIGN KEY
✔️ UNIQUE
✔️ CHECK
✔️ DEFAULT

---

🔹 1. PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies each record in a table.
✔️ A table can have only one PRIMARY KEY.
✔️ The PRIMARY KEY column cannot have NULL values.
✔️ It must be unique for every row.

✅ Example of PRIMARY KEY

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

✔️ StudentID is the PRIMARY KEY, meaning each student must have a unique ID and it cannot be NULL.

✅ Inserting Data (Valid & Invalid Cases)

INSERT INTO Students (StudentID, Name, Age) VALUES (1, 'Alice', 20); -- ✅ Valid
INSERT INTO Students (StudentID, Name, Age) VALUES (2, 'Bob', 22);   -- ✅ Valid
INSERT INTO Students (StudentID, Name, Age) VALUES (1, 'Charlie', 25); -- ❌ Error! Duplicate StudentID
INSERT INTO Students (StudentID, Name, Age) VALUES (NULL, 'David', 23); -- ❌ Error! NULL not allowed

---

🔹 2. FOREIGN KEY Constraint

A FOREIGN KEY creates a link between two tables.
✔️ It ensures referential integrity (data in the foreign key column must exist in the referenced table).
✔️ It prevents orphan records (a record that refers to a non-existing row in another table).

✅ Example of FOREIGN KEY

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50)
);

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

✔️ `StudentID` in Enrollments table must exist in the Students table.
✔️ `CourseID` in Enrollments table must exist in the Courses table.

✅ Inserting Data (Valid & Invalid Cases)

INSERT INTO Courses (CourseID, CourseName) VALUES (101, 'SQL Basics'); -- ✅ Valid
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID) VALUES (1, 1, 101); -- ✅ Valid (Student 1 exists)
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID) VALUES (2, 5, 101); -- ❌ Error! Student 5 does not exist

---

🔹 3. UNIQUE Constraint

Ensures all values in a column are unique, but it allows NULL values (unlike PRIMARY KEY).
✔️ Prevents duplicate values in a column.
✔️ A table can have multiple UNIQUE constraints (unlike PRIMARY KEY).

✅ Example of UNIQUE Constraint

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    Phone VARCHAR(15) UNIQUE
);

✔️ EmployeeID is the PRIMARY KEY (must be unique & non-null).
✔️ Email and Phone must be unique, but they can be NULL.

✅ Inserting Data (Valid & Invalid Cases)

INSERT INTO Employees (EmployeeID, Email, Phone) VALUES (1, 'alice@email.com', '1234567890'); -- ✅ Valid
INSERT INTO Employees (EmployeeID, Email, Phone) VALUES (2, 'bob@email.com', '9876543210'); -- ✅ Valid
INSERT INTO Employees (EmployeeID, Email, Phone) VALUES (3, 'alice@email.com', '5678901234'); -- ❌ Error! Duplicate Email
INSERT INTO Employees (EmployeeID, Email, Phone) VALUES (4, NULL, '5678901234'); -- ✅ Valid (NULL allowed in UNIQUE)

---

🔹 4. CHECK Constraint

CHECK ensures that column values meet specific conditions.
✔️ Used to enforce business rules (e.g., Age must be greater than 18).

✅ Example of CHECK Constraint

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT CHECK (Age >= 18)
);

✔️ Ensures that Age must be 18 or older.

✅ Inserting Data (Valid & Invalid Cases)

INSERT INTO Customers (CustomerID, Name, Age) VALUES (1, 'Alice', 25); -- ✅ Valid
INSERT INTO Customers (CustomerID, Name, Age) VALUES (2, 'Bob', 17); -- ❌ Error! Age must be >= 18

---

🔹 5. DEFAULT Constraint

1.29K views09:31