Welcome back! 👋
In the last lesson, we created our first database and tables.

In this lesson, we’ll dive deeper into data types and constraints, which are essential for building reliable databases.

By the end of this lesson, you’ll know:

1. What data types are and why they matter
2. How to choose the right data type for your table columns
3. What constraints are and why they’re important
4. How to apply constraints to ensure data integrity

What Are Data Types?

A data type defines the kind of data a column can hold.
Think of it like a container label — it tells the database, “This column will store numbers,” or “This column will store text,” etc.

Common Data Types:

Data Types in Our Tables

Let’s look at our customers table from Lesson 1:CREATE TABLE customers ( customer_id INT PRIMARY KEY AUTO_INCREMENT, full_name VARCHAR(100), email VARCHAR(120), city VARCHAR(50), country VARCHAR(50), signup_date DATE ); Copy

Why these types?

• customer_id INT → IDs are numbers
• full_name VARCHAR(100) → Names are text, 100 chars max
• signup_date DATE → Only the date, no time needed

Choosing correct data types helps the database run faster and store data efficiently.

What Are Constraints?

Constraints are rules we set on table columns to ensure data accuracy and integrity.

Think of them like guardrails for your data:

Adding Constraints to Tables

4a. Modify customers Table

Let’s make email unique and city required:ALTER TABLE customers MODIFY email VARCHAR(120) UNIQUE, MODIFY city VARCHAR(50) NOT NULL; Copy

✅ Now the database ensures:

• No duplicate emails
• Every customer must have a city

4b. Add a CHECK Constraint to order_items

We don’t want orders with negative quantity:ALTER TABLE order_items ADD CONSTRAINT chk_quantity CHECK (quantity > 0); Copy

✅ Now, SQL will reject any row where quantity <= 0.

4c. Set a DEFAULT Value

Let’s say all new customers default to country = ‘Pakistan’ if not provided:ALTER TABLE customers ALTER COLUMN country SET DEFAULT 'Pakistan'; Copy

Why Constraints Matter

Constraints help us:

1. Avoid mistakes (no duplicate emails, no negative quantities)
2. Keep data consistent (foreign keys link orders to real customers)
3. Make SQL queries reliable (we can trust the data)

A database without constraints is like a notebook where anyone can scribble anything — it becomes messy fast.

Practice Queries

Check constraints:

-- Try inserting a customer without city INSERT INTO customers (full_name, email) VALUES ('Ahmed Ali', 'ahmed@example.com'); -- This will fail because city is NOT NULL Copy-- Try inserting a duplicate email INSERT INTO customers (full_name, email, city) VALUES ('Ali Raza', 'ali@example.com', 'Lahore'); -- This will fail because email must be UNIQUE Copy-- Try inserting negative quantity INSERT INTO order_items (order_id, product_id, quantity, price_per_unit) VALUES (1, 2, -5, 45.00); -- This will fail due to CHECK constraint Copy

Lesson Summary

• Data types define what kind of data a column can hold
• Choosing the right data type improves performance and storage
• Constraints enforce rules on your data
• PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT are essential constraints
• Using constraints ensures data integrity

In Lesson 3, we will start basic SQL queries:

• SELECT statements
• Filtering data with WHERE
• Sorting with ORDER BY
• Using aggregate functions like COUNT, SUM, AVG