Part 6: Table Management, Constraints & Views

Table Management

Beginner

Tables are the foundation of every database. They hold your data in rows and columns, just like a spreadsheet. In a business application, you might have tables for employees, products, orders, customers, and departments. This section covers every way to create, modify, rename, and delete tables.

1 CREATE TABLE

Creates a brand-new table with the columns and data types you specify. Every column needs a name and a type (like INT for numbers, VARCHAR for text, DATE for dates). This is how you define the structure of your data before inserting anything.

SQL — Create a products table

CREATE TABLE products (
    product_id    INT            AUTO_INCREMENT PRIMARY KEY,
    sku           VARCHAR(20)    NOT NULL UNIQUE,
    product_name  VARCHAR(150)   NOT NULL,
    category      VARCHAR(50)    DEFAULT 'General',
    price         DECIMAL(10,2)  NULL,
    cost          DECIMAL(10,2)  NULL,
    stock         INT            NULL,
    is_active     BOOLEAN        DEFAULT TRUE,
    created_at    TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Output — DESCRIBE products

Field	Type	Null	Key	Default	Extra
product_id	int	NO	PRI	NULL	auto_increment
sku	varchar(20)	NO	UNI	NULL
product_name	varchar(150)	NO		NULL
category	varchar(50)	YES		General
price	decimal(10,2)	YES		NULL
cost	decimal(10,2)	YES		NULL
stock	int	YES		NULL
is_active	tinyint(1)	YES		1
created_at	timestamp	YES		CURRENT_TIMESTAMP	DEFAULT_GENERATED
updated_at	timestamp	YES		CURRENT_TIMESTAMP	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

Note: InnoDB is the default storage engine in MySQL 8.0. It supports transactions and foreign keys. Always use utf8mb4 for full Unicode support (including special characters in product descriptions).

2 CREATE TEMPORARY TABLE

A temporary table exists only during your current database session. Once you disconnect, it disappears automatically. Use it for staging data, intermediate calculations, or when you need a throwaway workspace. Other users cannot see your temporary table.

SQL — Create temp table for daily sales calculations

CREATE TEMPORARY TABLE temp_daily_sales (
    department_id   INT,
    category        VARCHAR(50),
    avg_revenue     DECIMAL(10,2),
    std_deviation   DECIMAL(10,4),
    order_count     INT
);

-- Populate with today's sales data
INSERT INTO temp_daily_sales
SELECT department_id, category,
       AVG(total), STDDEV(total), COUNT(*)
FROM orders
WHERE order_date = CURDATE()
GROUP BY department_id, category;

-- Use it for analysis
SELECT * FROM temp_daily_sales;

Output

department_id	category	avg_revenue	std_deviation	order_count
101	Electronics	249.95	85.3100	12
101	Accessories	34.50	12.0500	12
102	Clothing	67.80	21.4200	8

Temporary Table Lifecycle

Session Starts

➔

CREATE TEMPORARY TABLE

➔

Use it (INSERT, SELECT, etc.)

➔

Session Ends → Table Gone!

3 CREATE TABLE ... AS SELECT

Creates a new table and fills it with results from a SELECT query in one step. The new table's columns match the query's output columns. Great for creating archive copies or summary tables.

SQL — Archive high-value orders from January

CREATE TABLE big_orders_jan2026 AS
SELECT o.order_id,
       c.name AS customer_name,
       p.product_name,
       o.quantity,
       o.total,
       o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE o.total > 500
  AND o.order_date BETWEEN '2026-01-01' AND '2026-01-31';

Output — SELECT * FROM big_orders_jan2026

order_id	customer_name	product_name	quantity	total	order_date
4501	Alice Johnson	Laptop Pro 15	1	1299.00	2026-01-05
4623	Bob Smith	Standing Desk	2	899.00	2026-01-12
4801	Carol Davis	4K Monitor	3	1050.00	2026-01-18
4955	Dan Wilson	Ergonomic Chair	4	1596.00	2026-01-25

Note: The new table copies data but NOT constraints (no primary keys, foreign keys, or indexes). You need to add those separately with ALTER TABLE if needed.

4 TRUNCATE TABLE

Removes ALL rows from a table instantly. It is much faster than DELETE because it drops and re-creates the table internally instead of deleting one row at a time. It also resets AUTO_INCREMENT back to 1. You cannot use WHERE with TRUNCATE — it is all or nothing.

SQL

-- Remove all rows from the staging table
TRUNCATE TABLE temp_import_staging;

Before TRUNCATE — temp_import_staging

row_id	customer_name	product_name	amount
1	Alice Johnson	Laptop Pro 15	1299.00
2	Bob Smith	Standing Desk	449.50
3	Carol Davis	4K Monitor	350.00
4	Dan Wilson	Ergonomic Chair	399.00
5	Eve Martinez	Wireless Keyboard	79.99

▼ TRUNCATE TABLE temp_import_staging ▼

After TRUNCATE — temp_import_staging (0 rows)

row_id	customer_name	product_name	amount
Empty set (0 rows) — AUTO_INCREMENT reset to 1

TRUNCATE vs DELETE — Key Differences

Feature	TRUNCATE	DELETE
Speed	Very fast (drops + recreates)	Slower (row by row)
WHERE clause	Not supported	Supported
AUTO_INCREMENT	Resets to 1	Does not reset
Triggers	Does NOT fire triggers	Fires DELETE triggers
Rollback	Cannot be rolled back	Can be rolled back (in transaction)

5 RENAME TABLE

Changes the name of an existing table. All data, indexes, and constraints stay the same — only the name changes. Useful when restructuring your database or moving a table to an archive name.

SQL

-- Rename for archiving
RENAME TABLE orders TO orders_backup_2025;

-- You can rename multiple tables at once
RENAME TABLE
    old_customers    TO customers_archive,
    new_customers    TO customers;

Visual

orders

➔ RENAME TABLE ... TO ➔

orders_backup_2025

6 DROP TABLE

Permanently deletes a table and all of its data. The table structure, data, indexes, constraints, and triggers are all gone forever. There is no undo. Use with extreme caution.

SQL

-- Permanently delete the archived staging table
DROP TABLE temp_import_staging;

Effect of DROP TABLE

temp_import_staging (structure + data)

➔ DROP TABLE ➔

Gone forever! No undo.

Warning: If other tables have foreign keys referencing this table, the DROP will fail. You must drop the dependent tables first or remove the foreign key constraints.

7 DROP TABLE IF EXISTS

Same as DROP TABLE, but it does NOT throw an error if the table doesn't exist. This makes your scripts safer and repeatable — you can run them multiple times without crashing on a "table doesn't exist" error.

SQL

-- Safe cleanup at the start of a migration script
DROP TABLE IF EXISTS temp_import_staging;
DROP TABLE IF EXISTS old_audit_log;
DROP TABLE IF EXISTS migration_temp;

Comparison: DROP TABLE vs DROP TABLE IF EXISTS

DROP TABLE (table doesn't exist)

ERROR 1051 (42S02): Unknown table 'shop_db.temp_import_staging'

DROP TABLE IF EXISTS (table doesn't exist)

Query OK, 0 rows affected, 1 warning (0.00 sec)

8 ALTER TABLE ADD COLUMN

Adds a new column to an existing table. You can place it at a specific position using AFTER or FIRST, or let it go at the end by default. Existing rows will have NULL (or the DEFAULT value) in the new column.

SQL

ALTER TABLE products
ADD COLUMN warehouse VARCHAR(50) DEFAULT 'Main' AFTER product_name;

DESCRIBE products — Before

Field	Type	Null	Key	Default
product_id	int	NO	PRI	NULL
sku	varchar(20)	NO	UNI	NULL
product_name	varchar(150)	NO		NULL
category	varchar(50)	YES		General
... (more columns)

▼ ALTER TABLE ADD COLUMN warehouse ▼

DESCRIBE products — After

Field	Type	Null	Key	Default
product_id	int	NO	PRI	NULL
sku	varchar(20)	NO	UNI	NULL
product_name	varchar(150)	NO		NULL
warehouse	varchar(50)	YES		Main
category	varchar(50)	YES		General
... (more columns)

9 ALTER TABLE DROP COLUMN

Removes a column from a table permanently. All data in that column is lost. You cannot drop the last remaining column in a table.

SQL

ALTER TABLE products
DROP COLUMN warehouse;

DESCRIBE products — Before

Field	Type	Null	Key	Default
product_id	int	NO	PRI	NULL
sku	varchar(20)	NO	UNI	NULL
product_name	varchar(150)	NO		NULL
warehouse	varchar(50)	YES		Main
category	varchar(50)	YES		General

▼ ALTER TABLE DROP COLUMN warehouse ▼

DESCRIBE products — After

Field	Type	Null	Key	Default
product_id	int	NO	PRI	NULL
sku	varchar(20)	NO	UNI	NULL
product_name	varchar(150)	NO		NULL
category	varchar(50)	YES		General

10 ALTER TABLE MODIFY COLUMN

Changes the data type, size, or properties of an existing column without renaming it. For example, you might need to increase a VARCHAR length, add NOT NULL, or change a column from INT to BIGINT.

SQL — Increase product_name length and make it NOT NULL

ALTER TABLE products
MODIFY COLUMN product_name VARCHAR(255) NOT NULL;

Column Change: Before vs After

Before

Field	Type	Null
product_name	varchar(150)	NO

➔

After

Field	Type	Null
product_name	varchar(255)	NO

Caution: If you reduce a column's size (e.g., VARCHAR(255) → VARCHAR(50)), any existing data longer than 50 characters will be truncated, causing data loss!

11 ALTER TABLE RENAME COLUMN

Renames a column without changing its type or properties. Introduced in MySQL 8.0 as a simpler alternative to the older CHANGE COLUMN syntax.

SQL

ALTER TABLE products
RENAME COLUMN category TO department;

Column Rename

category (VARCHAR(50))

➔ RENAME COLUMN ➔

department (VARCHAR(50))

Data type, default, and constraints remain unchanged.

12 ALTER TABLE ADD CONSTRAINT

Adds a constraint (like a foreign key, unique constraint, or check constraint) to an existing table. This is how you enforce rules on data that was created without them, or when you need to add rules after the fact.

SQL — Add constraints to an existing table

-- Add a CHECK constraint to ensure prices are positive
ALTER TABLE orders
ADD CONSTRAINT chk_total_positive
    CHECK (total >= 0);

-- Add a FOREIGN KEY constraint
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

Constraint Added to Existing Table

orders table (no constraint)

➔ ALTER TABLE ADD CONSTRAINT ➔

orders table (FK + CHECK enforced)

13 ALTER TABLE ADD INDEX

Adds an index to one or more columns to speed up queries that filter or sort by those columns. Think of it like adding tabs to a phone book — it helps MySQL find rows faster without scanning the entire table.

SQL

-- Single-column index for fast lookups by product_id
ALTER TABLE orders
ADD INDEX idx_orders_product_id (product_id);

-- Composite index for date + status queries
ALTER TABLE orders
ADD INDEX idx_orders_date_status (order_date, status);

Output — SHOW INDEX FROM orders

Table	Non_unique	Key_name	Seq	Column_name
orders	0	PRIMARY	1	order_id
orders	1	idx_orders_product_id	1	product_id
orders	1	idx_orders_date_status	1	order_date
orders	1	idx_orders_date_status	2	status

When to Add Indexes

Columns frequently used in WHERE clauses
Columns used in JOIN conditions (foreign keys)
Columns used in ORDER BY or GROUP BY
Do NOT over-index — each index slows down INSERT/UPDATE operations

Constraints

Beginner

Constraints are rules you apply to columns to protect the quality of your data. In a business application, bad data can cause real problems — a duplicate customer ID, a negative order total, or a missing product name could lead to costly errors. Constraints prevent bad data from ever entering your tables.

1 PRIMARY KEY

A primary key uniquely identifies each row in a table. It automatically enforces two rules: the value must be UNIQUE (no duplicates) and it must be NOT NULL (cannot be empty). Every table should have a primary key.

SQL

CREATE TABLE customers (
    customer_id  INT          AUTO_INCREMENT PRIMARY KEY,
    email        VARCHAR(100) NOT NULL UNIQUE,
    name         VARCHAR(100) NOT NULL,
    city         VARCHAR(100) NOT NULL,
    country      VARCHAR(50)  NOT NULL
);

PRIMARY KEY Enforces Uniqueness + NOT NULL

Rule 1: UNIQUE

No two rows can have the same primary key value. customer_id = 1 can exist only ONCE.

Rule 2: NOT NULL

Every row MUST have a primary key value. NULL is never allowed.

customer_id (PK)	email	name
1	alice@example.com	Alice
2	bob@example.com	Bob
3	carol@example.com	Carol
1	dan@example.com	Dan ← ERROR! Duplicate PK
NULL	eve@example.com	Eve ← ERROR! NULL PK

2 FOREIGN KEY with ON DELETE CASCADE

A foreign key links a column in one table (child) to the primary key of another table (parent). ON DELETE CASCADE means: "If the parent row is deleted, automatically delete all matching child rows too." Use when child data has no meaning without the parent.

SQL

CREATE TABLE orders (
    order_id     INT AUTO_INCREMENT PRIMARY KEY,
    customer_id  INT NOT NULL,
    order_date   DATE NOT NULL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

-- Deleting customer 2 will automatically delete their orders
DELETE FROM customers WHERE customer_id = 2;

ON DELETE CASCADE — Deleting Parent Removes Children

Before: DELETE FROM customers WHERE customer_id = 2

customers (Parent)

1 | Alice Johnson

2 | Bob Smith ← DELETE

3 | Carol Davis

← FK →

orders (Child)

101 | customer_id: 1

102 | customer_id: 2 ← AUTO-DELETE

103 | customer_id: 2 ← AUTO-DELETE

104 | customer_id: 3

After CASCADE:

customers

1 | Alice Johnson

3 | Carol Davis

orders

101 | customer_id: 1

104 | customer_id: 3

3 FOREIGN KEY with ON DELETE SET NULL

When the parent row is deleted, the child's foreign key column is set to NULL instead of deleting the child row. Use when the child data still has value even without the parent — for example, keeping an order record even if the sales representative's account is removed.

SQL

CREATE TABLE orders_v2 (
    order_id        INT AUTO_INCREMENT PRIMARY KEY,
    customer_id     INT NOT NULL,
    employee_id     INT NULL,    -- Must allow NULL!
    order_date      DATE NOT NULL,
    FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
        ON DELETE SET NULL
);

-- Delete employee 5 (Sales Rep Bob)
DELETE FROM employees WHERE employee_id = 5;

ON DELETE SET NULL — Child Keeps Row, FK Becomes NULL

Before DELETE

employees (Parent)

4 | Alice Johnson

5 | Bob Smith ← DELETE

orders_v2 (Child)

101 | employee_id: 4

102 | employee_id: 5

103 | employee_id: 5

After SET NULL:

employees

4 | Alice Johnson

orders_v2

101 | employee_id: 4

102 | employee_id: NULL

103 | employee_id: NULL

Orders are preserved but the employee link is removed.

4 FOREIGN KEY with ON DELETE RESTRICT

Prevents deleting a parent row if any child rows reference it. The DELETE fails with an error. This is the safest option — use it when you never want to accidentally orphan or lose data. This is also the default behavior if you don't specify ON DELETE.

SQL

CREATE TABLE order_items (
    item_id      INT AUTO_INCREMENT PRIMARY KEY,
    order_id     INT NOT NULL,
    product_id   INT,
    quantity     INT,
    FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
        ON DELETE RESTRICT
);

-- Try to delete an order that has items
DELETE FROM orders WHERE order_id = 101;

ON DELETE RESTRICT — Parent Deletion BLOCKED

orders (Parent)

101 | customer_id: 1 ← TRY TO DELETE

102 | customer_id: 2

✘

order_items (Child)

501 | order_id: 101 ← EXISTS!

502 | order_id: 101 ← EXISTS!

503 | order_id: 102

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`shop_db`.`order_items`, CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`))

Which ON DELETE Should You Choose?

CASCADE: When child data is meaningless without parent (order line items, shopping cart entries)
SET NULL: When child data is still valuable (keep orders even if sales rep leaves)
RESTRICT: When safety is critical (never delete customers who have orders)

5 UNIQUE

Ensures no two rows have the same value in this column (or combination of columns). Unlike PRIMARY KEY, a UNIQUE column CAN contain NULL values. Use it for things like email addresses, product SKUs, or employee badge numbers.

SQL

CREATE TABLE departments (
    department_id   INT AUTO_INCREMENT PRIMARY KEY,
    dept_code       VARCHAR(50) UNIQUE,
    dept_name       VARCHAR(100) NOT NULL
);

-- This works fine
INSERT INTO departments (dept_code, dept_name)
VALUES ('ENG-001', 'Engineering');

-- This also works fine (different code)
INSERT INTO departments (dept_code, dept_name)
VALUES ('MKT-001', 'Marketing');

-- This FAILS (duplicate department code)
INSERT INTO departments (dept_code, dept_name)
VALUES ('ENG-001', 'Design');

UNIQUE Constraint in Action

department_id	dept_code	dept_name	Result
1	ENG-001	Engineering	OK
2	MKT-001	Marketing	OK
—	ENG-001	Design	ERROR

ERROR 1062 (23000): Duplicate entry 'ENG-001' for key 'departments.dept_code'

6 NOT NULL

Ensures a column can never contain a NULL value. Every INSERT must provide a value for this column (unless there is a DEFAULT). Use it for critical fields like customer names, product names, and dates.

SQL

CREATE TABLE employees (
    employee_id     INT AUTO_INCREMENT PRIMARY KEY,
    department_id   INT          NOT NULL,
    name            VARCHAR(100) NOT NULL,
    hire_date       DATE         NOT NULL,
    notes           TEXT         NULL      -- this one CAN be null
);

-- This works (all NOT NULL fields have values)
INSERT INTO employees (department_id, name, hire_date)
VALUES (1, 'Alice Johnson', '2026-02-23');

-- This FAILS (name is NULL)
INSERT INTO employees (department_id, name, hire_date)
VALUES (2, NULL, '2026-02-23');

NOT NULL Enforcement

department_id	name	hire_date	Result
1	Alice Johnson	2026-02-23	OK
2	NULL	2026-02-23	ERROR

ERROR 1048 (23000): Column 'name' cannot be null

7 DEFAULT Value

Sets an automatic value for a column when no value is provided during INSERT. It saves you from having to specify common values every time. For example, a default status of 'Pending' for new orders.

SQL

CREATE TABLE orders_v3 (
    order_id     INT AUTO_INCREMENT PRIMARY KEY,
    customer_id  INT NOT NULL,
    status       VARCHAR(20) DEFAULT 'Pending',
    priority     VARCHAR(10) DEFAULT 'Normal',
    created_at   TIMESTAMP   DEFAULT CURRENT_TIMESTAMP
);

-- Insert WITHOUT specifying status, priority, or created_at
INSERT INTO orders_v3 (customer_id)
VALUES (1), (2), (3);

Output — SELECT * FROM orders_v3

order_id	customer_id	status	priority	created_at
1	1	Pending	Normal	2026-02-23 08:00:00
2	2	Pending	Normal	2026-02-23 08:00:00
3	3	Pending	Normal	2026-02-23 08:00:00

How Defaults Work

We only inserted customer_id, but the status, priority, and created_at columns were automatically filled with their DEFAULT values. You can still override a default by providing an explicit value.

8 CHECK (Condition)

Validates data against a condition before allowing an INSERT or UPDATE. If the condition evaluates to FALSE, the operation is rejected. Use it to enforce business rules like "salary must be positive" or "status must be one of the allowed values."

SQL

CREATE TABLE employees_v2 (
    employee_id   INT AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(150) NOT NULL,
    salary        DECIMAL(10,2),
    status        VARCHAR(20),
    CONSTRAINT chk_salary_valid
        CHECK (salary BETWEEN 0 AND 999999.99),
    CONSTRAINT chk_status
        CHECK (status IN ('Active','Inactive','On Leave','Terminated','Probation'))
);

-- Valid insert
INSERT INTO employees_v2 (name, salary, status)
VALUES ('Alice Johnson', 75000.00, 'Active');  -- OK

-- Invalid salary
INSERT INTO employees_v2 (name, salary, status)
VALUES ('Bob Smith', -500, 'Active');   -- ERROR!

-- Invalid status
INSERT INTO employees_v2 (name, salary, status)
VALUES ('Carol Davis', 60000, 'Retired');     -- ERROR!

CHECK Constraint Validation

name	salary	status	Result
Alice Johnson	75000.00	Active	OK
Bob Smith	-500	Active	FAILED
Carol Davis	60000	Retired	FAILED

ERROR 3819 (HY000): Check constraint 'chk_salary_valid' is violated.

9 AUTO_INCREMENT

Automatically generates a unique, incrementing number for each new row. You do not need to provide a value for this column during INSERT — MySQL handles it. Perfect for primary key IDs where you just want sequential numbers.

SQL

CREATE TABLE audit_trail (
    log_id     INT AUTO_INCREMENT PRIMARY KEY,
    action     VARCHAR(50) NOT NULL,
    user_name  VARCHAR(100),
    log_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert without specifying log_id
INSERT INTO audit_trail (action, user_name) VALUES
    ('LOGIN',          'admin'),
    ('VIEW_PRODUCTS',  'admin'),
    ('CREATE_ORDER',   'alice'),
    ('APPROVE_ORDER',  'manager_bob'),
    ('LOGOUT',         'admin');

Output — SELECT * FROM audit_trail

log_id	action	user_name	log_time
1	LOGIN	admin	2026-02-23 08:00:01
2	VIEW_PRODUCTS	admin	2026-02-23 08:00:01
3	CREATE_ORDER	alice	2026-02-23 08:00:01
4	APPROVE_ORDER	manager_bob	2026-02-23 08:00:01
5	LOGOUT	admin	2026-02-23 08:00:01

AUTO_INCREMENT Behavior

Each new row gets the next number (1, 2, 3, 4, 5...). If you delete row 3 and insert again, the next ID will be 6, not 3. MySQL never reuses auto-increment values (unless you TRUNCATE the table, which resets it to 1).

Constraint Cheat Sheet

All MySQL Constraints at a Glance

Constraint	What It Does	When to Use It	On Violation
`PRIMARY KEY`	Unique identifier for each row. Combines UNIQUE + NOT NULL.	Every table must have one. Usually an auto-increment INT column.	ERROR: Duplicate entry / Column cannot be null
`FOREIGN KEY`	Links child table to parent table. Enforces referential integrity.	When a column references a row in another table (customer_id, order_id).	ERROR: Cannot add/update (invalid ref) or Cannot delete (RESTRICT)
`UNIQUE`	No duplicate values allowed in the column.	Email, SKU, badge numbers, department codes — any natural identifier.	ERROR: Duplicate entry for key
`NOT NULL`	Column cannot contain NULL.	Required fields: customer name, product name, dates, any critical data.	ERROR: Column cannot be null
`DEFAULT`	Auto-fills a value when none is provided.	Status = 'Pending', priority = 'Normal', timestamps.	N/A — it prevents the need for violation by filling in the value.
`CHECK`	Validates data against a boolean condition.	Salary ranges, valid statuses, positive values, enum-like validations.	ERROR: Check constraint is violated
`AUTO_INCREMENT`	Generates sequential unique integers automatically.	Primary key ID columns where you want auto-generated numbers.	N/A — MySQL handles the value generation.

Views

Intermediate

A view is a virtual table — it does not store any data itself. Instead, it saves a SELECT query and runs it every time you query the view. Views simplify complex queries, provide a security layer (hiding sensitive columns), and make your code cleaner by giving a friendly name to complicated joins.

View = Saved Query (No Data Stored)

SELECT * FROM order_summary

▼ View runs underlying query ▼

SELECT c.name, o.order_date, o.total
FROM customers c JOIN orders o JOIN products p ...

▼ Query reads from real tables ▼

customers

orders

products

The view does not store data. Every time you SELECT from it, MySQL re-executes the underlying query against the real tables.

1 CREATE VIEW

Creates a named virtual table based on a SELECT query. After creating it, you can SELECT from it just like a regular table. It simplifies repeated complex queries.

SQL — Create a view for pending orders

CREATE VIEW v_pending_orders AS
SELECT
    o.order_id,
    c.email,
    c.name AS customer_name,
    o.order_date,
    o.priority,
    o.status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'Pending'
ORDER BY
    CASE o.priority
        WHEN 'Rush' THEN 1
        WHEN 'High' THEN 2
        ELSE 3
    END,
    o.order_date;

Using the view — SELECT * FROM v_pending_orders

order_id	email	customer_name	order_date	priority	status
1045	bob@example.com	Bob Smith	2026-02-23	Rush	Pending
1042	alice@example.com	Alice Johnson	2026-02-23	High	Pending
1038	carol@example.com	Carol Davis	2026-02-22	Normal	Pending
1040	dan@example.com	Dan Wilson	2026-02-22	Normal	Pending
1041	eve@example.com	Eve Martinez	2026-02-23	Normal	Pending

Benefits of Views

Simplicity: Write the complex join once, then just SELECT * FROM v_pending_orders
Security: Grant access to the view without granting access to the underlying tables
Consistency: Everyone uses the same query logic — no chance of someone writing the join wrong

2 CREATE OR REPLACE VIEW

Updates an existing view with a new query definition. If the view does not exist yet, it creates it. This is safer than dropping and recreating because any permissions granted on the view are preserved.

SQL — Update the pending orders view to include item count

CREATE OR REPLACE VIEW v_pending_orders AS
SELECT
    o.order_id,
    c.email,
    c.name AS customer_name,
    o.order_date,
    o.priority,
    o.status,
    COUNT(oi.item_id) AS item_count    -- NEW column added
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'Pending'
GROUP BY o.order_id, c.email, customer_name, o.order_date, o.priority, o.status;

Output — SELECT * FROM v_pending_orders (updated view)

order_id	email	customer_name	order_date	priority	status	item_count
1045	bob@example.com	Bob Smith	2026-02-23	Rush	Pending	5
1042	alice@example.com	Alice Johnson	2026-02-23	High	Pending	3
1038	carol@example.com	Carol Davis	2026-02-22	Normal	Pending	2
1040	dan@example.com	Dan Wilson	2026-02-22	Normal	Pending	7
1041	eve@example.com	Eve Martinez	2026-02-23	Normal	Pending	1

CREATE OR REPLACE vs DROP + CREATE

CREATE OR REPLACE VIEW

Single statement
Preserves GRANT permissions
Atomic operation

DROP VIEW + CREATE VIEW

Two statements
Loses GRANT permissions on DROP
Brief window where view does not exist

3 DROP VIEW

Permanently removes a view definition from the database. Since views do not store data, dropping a view does NOT delete any data from the underlying tables. It only removes the saved query.

SQL

-- Remove the view
DROP VIEW v_pending_orders;

-- Safe version (no error if view doesn't exist)
DROP VIEW IF EXISTS v_pending_orders;

What DROP VIEW Removes

v_pending_orders (View Definition) — DELETED

but

customers, orders, order_items — UNTOUCHED

The underlying tables and all their data remain completely unaffected.

4 Practical View: Customer Summary

A real-world example that demonstrates the power of views. This view joins three tables — customers, orders, and order_items — to create a convenient "customer summary" that shows each customer's order activity without writing a complex query every time.

SQL — Create a comprehensive customer summary view

CREATE VIEW v_customer_summary AS
SELECT
    c.customer_id,
    c.email,
    c.name AS customer_name,
    c.city,
    c.country,
    COUNT(DISTINCT o.order_id)    AS total_orders,
    COUNT(DISTINCT oi.item_id)    AS total_items,
    SUM(CASE WHEN o.total > 500 THEN 1 ELSE 0 END) AS big_order_count,
    MAX(o.order_date)             AS last_order_date,
    SUM(o.total)                  AS lifetime_spend
FROM customers c
LEFT JOIN orders o       ON c.customer_id = o.customer_id
LEFT JOIN order_items oi  ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.email, customer_name, c.city, c.country;

Usage — Simple queries against the view

-- Get all customers with high-value orders
SELECT customer_name, total_orders, big_order_count
FROM v_customer_summary
WHERE big_order_count > 0
ORDER BY big_order_count DESC;

Output

customer_name	total_orders	big_order_count
Bob Smith	12	4
Alice Johnson	8	2
Carol Davis	5	1

Another query on the same view

-- Find customers who haven't ordered in 30 days
SELECT customer_name, email, last_order_date
FROM v_customer_summary
WHERE last_order_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY last_order_date;

Output

customer_name	email	last_order_date
Eve Martinez	eve@example.com	2026-01-10
Dan Wilson	dan@example.com	2026-01-18

What v_customer_summary Hides From You

Instead of writing a 3-table JOIN with GROUP BY, aggregates, and CASE expressions every time, you just write:

customers
customer_id, email, name, city

orders
order_id, customer_id, total

order_items
item_id, order_id, product_id

▼ JOIN + GROUP BY + Aggregates ▼

v_customer_summary
customer_name, total_orders, total_items, big_order_count, last_order_date, lifetime_spend ...

One simple view replaces a complex multi-table query!

Business Database View Ideas

v_pending_orders — Orders waiting to be shipped
v_low_stock — All products below minimum stock threshold
v_daily_revenue — Sales totals per department today
v_top_customers — Customers ranked by lifetime spending
v_order_turnaround — Average time from order to delivery

Part 6: Table Management, Constraints & Views — MySQL 8.0 Learning Guide

Business Database Edition • Back to Full Guide

Part 6 of MySQL 8.0 Learning Guide

Part 6: Table Management, Constraints & Views

Table of Contents

1 CREATE TABLE

2 CREATE TEMPORARY TABLE

3 CREATE TABLE ... AS SELECT

4 TRUNCATE TABLE

5 RENAME TABLE

6 DROP TABLE

7 DROP TABLE IF EXISTS

8 ALTER TABLE ADD COLUMN

9 ALTER TABLE DROP COLUMN

10 ALTER TABLE MODIFY COLUMN

11 ALTER TABLE RENAME COLUMN

12 ALTER TABLE ADD CONSTRAINT

13 ALTER TABLE ADD INDEX

1 PRIMARY KEY

2 FOREIGN KEY with ON DELETE CASCADE

3 FOREIGN KEY with ON DELETE SET NULL

4 FOREIGN KEY with ON DELETE RESTRICT

5 UNIQUE

6 NOT NULL

7 DEFAULT Value

8 CHECK (Condition)

9 AUTO_INCREMENT

Constraint Cheat Sheet

All MySQL Constraints at a Glance

1 CREATE VIEW

2 CREATE OR REPLACE VIEW

3 DROP VIEW

4 Practical View: Customer Summary