Keeping Data Clean with UNIQUE and NOT NULL Constraints

Unique and NOT NULL Constraints

Imagine you are making a school register.

• Every student must have a roll number → it cannot be empty
• No two students can share the same roll number

That’s exactly what NOT NULL and UNIQUE constraints do in a database

We’ll learn this using MySQL.

---

What Are Constraints? (Super Simple)

Constraints are rules that tell the database:

“Hey! Data must behave nicely!”

They protect your data from mistakes.

---

NOT NULL Constraint

• NOT NULL = You must fill this box
• Empty value Not allowed

Example:

• Name cannot be empty
• Email cannot be empty

---

Creating a Table with NOT NULL

CREATE TABLE students (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    age INT
);

Allowed

INSERT INTO students VALUES (1, 'Amit', 10);

Not Allowed

INSERT INTO students VALUES (2, NULL, 11);

Error:
Column 'name' cannot be null

---

Adding NOT NULL to Existing Table

ALTER TABLE students
MODIFY age INT NOT NULL;

---

UNIQUE Constraint

• UNIQUE = No copying allowed
• Each value must be different

Example:

• Email IDs
• Phone numbers
• Aadhaar numbers

---

Creating a Table with UNIQUE

CREATE TABLE users (
    user_id INT,
    email VARCHAR(100) UNIQUE
);

Allowed

INSERT INTO users VALUES (1, 'a@gmail.com');
INSERT INTO users VALUES (2, 'b@gmail.com');

Not Allowed

INSERT INTO users VALUES (3, 'a@gmail.com');

Error:
Duplicate entry 'a@gmail.com' for key 'email'

---

Adding UNIQUE Constraint Later

ALTER TABLE users
ADD UNIQUE (email);

---

UNIQUE vs NOT NULL (Easy Comparison)

Rule	NOT NULL	UNIQUE
Empty value allowed?	No	Yes (only once)
Duplicate allowed?	Yes	No
Purpose	Mandatory data	No duplicates

---

Using BOTH Together (Very Common!)

CREATE TABLE employees (
    emp_id INT NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE
);

Meaning:

• emp_id → always present + no duplicates
• email → always present + no duplicates

---

Real-Life Example (Super Important)

CREATE TABLE bank_accounts (
    account_number BIGINT NOT NULL UNIQUE,
    customer_name VARCHAR(50) NOT NULL,
    balance DECIMAL(10,2)
);

Why?

• Account number must exist
• No two people can have the same account number

---

Practice Questions (With Solutions)

Question 1

Create a table products where:

• product_id must be unique and not null
• product_name must not be null

Solution

CREATE TABLE products (
    product_id INT NOT NULL UNIQUE,
    product_name VARCHAR(50) NOT NULL
);

---

Question 2

Insert a row with a NULL product name. What happens?

Answer

INSERT INTO products VALUES (1, NULL);

Error because product_name is NOT NULL

---

Question 3

Can a UNIQUE column have NULL values?

Answer

Yes
But only one NULL value is allowed.

---

Interview Questions & Answers

Q1: What is NOT NULL constraint?

Answer:
It ensures a column cannot store NULL (empty) values.

---

Q2: What is UNIQUE constraint?

Answer:
It ensures all values in a column are different.

---

Q3: Can a table have multiple UNIQUE constraints?

Answer:
Yes, multiple columns can be UNIQUE.

---

Q4: Difference between PRIMARY KEY and UNIQUE?

Answer:

PRIMARY KEY	UNIQUE
Cannot be NULL	Can be NULL
Only one per table	Multiple allowed

---

🔹 Q5: Can UNIQUE allow duplicate NULLs?

Answer:
No, only one NULL value is allowed.

---

Kid-Level Summary

• NOT NULL → “You must fill this.”
• UNIQUE → “No copying.”
• Both together → “Must be filled AND different.”

Constraints = Database teachers who keep data disciplined