See how data is structured in relational databases and understand the rules that make a table reliable.
Save
Complete lesson & earn 250 PX
Every table has columns (what you store) and rows (each individual record). Rules like Primary Key keep data clean.
EXERCISE
3A Foreign Key links rows between tables and lets the database automatically enforce that relationships stay valid.
Save
The problem Foreign Keys solve:
Without a foreign key, nothing stops you from creating an order for a customer that doesn''t exist:
-- Without FK constraint, this silently succeeds with a ghost customer:
INSERT INTO orders (customer_id, total) VALUES (99999, 500);
-- customer_id 99999 doesn't exist — corrupt data, no error
Foreign Key in action:
-- Parent table:
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
-- Child table with Foreign Key:
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
total DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE -- if customer deleted, their orders are deleted too
ON UPDATE CASCADE -- if customer id changes, orders update automatically
);
ON DELETE options:
ON DELETE CASCADE -- delete child rows automatically (orders deleted with customer)
ON DELETE RESTRICT -- block deletion if child rows exist (default, safest)
ON DELETE SET NULL -- set FK column to NULL when parent deleted
💡 Key Insight:
ON DELETE RESTRICT(the default) is usually what you want. It forces you to clean up child records first, preventing accidental data loss.
EXERCISE
1Every column has a data type that tells MySQL what kind of value it stores. Choosing the right type keeps your data accurate and your queries fast.
The most common MySQL data types:
-- Text types:
VARCHAR(255) -- variable-length text, up to 255 chars (most common)
TEXT -- longer text, no length limit, can't be indexed easily
CHAR(10) -- fixed-length text (always exactly 10 chars)
-- Number types:
INT -- whole numbers: -2,147,483,648 to 2,147,483,647
BIGINT -- very large whole numbers (use for ids in big systems)
DECIMAL(10,2) -- exact decimals, e.g. 10 digits total, 2 after point
FLOAT / DOUBLE -- approximate decimals (avoid for money!)
-- Date / Time types:
DATE -- '2024-01-15' (date only)
DATETIME -- '2024-01-15 14:30:00' (date + time)
TIMESTAMP -- like DATETIME but auto-updates, timezone-aware
-- Boolean:
TINYINT(1) -- MySQL uses 0/1 for false/true (no native BOOLEAN)
Practical example — creating a products table:
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock_qty ,
is_active TINYINT() ,
created_at DATETIME
);
Save
EXERCISE
2Constraints are rules that protect your data. A Primary Key uniquely identifies every row. Other constraints stop bad data from ever entering the table.
Primary Key — the row''s identity:
-- Every table should have one PRIMARY KEY
-- It must be: unique, never NULL, never changing
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY, -- auto-generates: 1, 2, 3...
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE -- no two customers with same email
);
All five main constraints:
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY, -- 1. PRIMARY KEY: unique row id
customer_id INT NOT NULL, -- 2. NOT NULL: must have a value
total DECIMAL(10,2) DEFAULT 0.00, -- 3. DEFAULT: fallback value
status VARCHAR(20) DEFAULT 'pending',
email VARCHAR(150) UNIQUE, -- 4. UNIQUE: no duplicates in column
-- 5. FOREIGN KEY: enforces the relationship to customers table
CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
What happens when a constraint is violated:
Save
💡 Key Insight: Never store money as FLOAT — floating-point arithmetic is imprecise.
0.1 + 0.2in FLOAT can give0.30000000000000004. UseDECIMAL(10,2)for anything financial.
-- This will FAIL — customer_id 999 doesn't exist in customers:
INSERT INTO orders (customer_id, total)
VALUES (999, 500.00);
-- Error: Cannot add or update a child row: a foreign key constraint fails
-- This will FAIL — email already exists:
INSERT INTO customers (name, email)
VALUES ('Test', 'arjun@example.com');
-- Error: Duplicate entry for key 'email'
💡 Key Insight: Constraints are your safety net. They prevent bad data from entering the and save you from corrupt records weeks later.