Need to change a table structure after it's created? Use ALTER TABLE!
🎯 Real-Life Analogy: ALTER TABLE is Like Renovating a Building
Your house (table) is already built, but you want to:
Add a new room: ALTER TABLE ADD COLUMN
Remove a room: ALTER TABLE DROP COLUMN
Rename the house: ALTER TABLE RENAME TO
Remodel a room: ALTER TABLE MODIFY COLUMN
ALTER lets you modify without tearing down and rebuilding!
1. Adding New Columns:
Try It: Add Column to Existing Table
Output will appear here...
ALTER TABLE ADD COLUMN Syntax:
ALTER TABLE table_name
ADD COLUMN column_name data_type constraints;
Note: Existing rows will have NULL for the new column (unless you specify
DEFAULT)
2. Renaming Tables:
Try It: Rename a Table
Output will appear here...
🔍 SQLite ALTER Limitations:
SQLite has limited ALTER capabilities compared to other databases:
✅ Can: ADD COLUMN
✅ Can: RENAME TABLE
✅ Can: RENAME COLUMN (SQLite 3.25+)
❌ Cannot: DROP COLUMN (need workaround)
❌ Cannot: MODIFY COLUMN (need workaround)
❌ Cannot: Add/Remove constraints on existing columns
MySQL and PostgreSQL support more ALTER operations!
Workaround: Recreating Table to Remove Column
-- Steps to remove a column in SQLite:
-- 1. Create new table without the unwanted column
CREATE TABLE employees_new (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
-- removed 'age' column
);
-- 2. Copy data from old table
INSERT INTO employees_new SELECT id, name FROM employees_old;
-- 3. Drop old table
DROP TABLE employees_old;
-- 4. Rename new table
ALTER TABLE employees_new RENAME TO employees_old;
🗑️ DROP TABLE - Deleting Tables
Need to completely remove a table? Use DROP TABLE (but be careful!)
⚠️ Warning: DROP TABLE is Permanent!
Think of DROP TABLE like demolishing a building:
The table structure is completely deleted
All data inside is permanently lost
There's NO UNDO button!
Always backup your data before using DROP TABLE!
DROP TABLE Syntax:
DROP TABLE table_name;
-- Safer version (won't error if table doesn't exist):
DROP TABLE IF EXISTS table_name;
Try It: Create and Drop a Test Table
Output will appear here...
Safe Dropping Pattern:
-- Check if table exists before dropping
DROP TABLE IF EXISTS old_data;
-- Create fresh table
CREATE TABLE new_data (
id INTEGER PRIMARY KEY,
info TEXT
);
-- This won't error even if old_data didn't exist
🏗️ Schema Design Basics
Learn to design good database structures - the foundation of great applications!
🎯 What is a Schema?
A database schema is the blueprint of your database - the overall structure of
tables, columns, relationships, and constraints.
Think of it like architectural plans for a house:
Tables: Rooms in the house
Columns: Features of each room
Relationships: How rooms connect (hallways, doors)
Constraints: Building codes and rules
Good Schema Design Principles:
1. Normalization
Avoid data duplication
Store each piece of info once
2. Meaningful Names
Use clear table/column names
users, products, order_items
3. Primary Keys
Every table needs a unique ID
Use INTEGER AUTOINCREMENT
4. Right Data Types
Choose appropriate types
INTEGER for counts, REAL for prices
5. Constraints
Enforce data integrity
NOT NULL, UNIQUE, CHECK
6. Documentation
Add comments explaining tables
Make intent clear
Example: Well-Designed E-Commerce Schema
-- Customers table
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULL UNIQUE,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
-- Products table
CREATE TABLE products (
product_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
description TEXT,
price REAL NOT NULL CHECK(price >= 0),
stock_quantity INTEGER DEFAULT 0
);
-- Orders table
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER NOT NULL,
order_date TEXT DEFAULT CURRENT_TIMESTAMP,
total_amount REAL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- Order Items (junction table for many-to-many)
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER NOT NULL CHECK(quantity > 0),
price_at_purchase REAL NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
🔍 Why This Design is Good:
No duplication: Customer info stored once, not repeated in every order
Clear relationships: Foreign keys link tables properly
Data integrity: Constraints prevent invalid data (negative prices, empty
names)
Scalable: Can add new products/customers without redesigning
Task: Create a table called "library_books" with columns for: id (primary key),
title (required), author (required), isbn (unique), year_published (integer), available (default
1 for true).