Chapter 4: Creating and Managing Tables
Up until now, we’ve created databases (like empty filing cabinets). Now we’re going to fill them with actual tables — the spreadsheets inside the cabinet where all your data lives.
Today we’re going to learn four very important commands:
- CREATE TABLE → Build a new table (define columns, data types, rules)
- DROP TABLE → Delete a table completely
- ALTER TABLE → Change an existing table (add, modify, or drop columns)
- TRUNCATE TABLE → Empty a table (delete all rows but keep the structure)
I’ll explain each one like we’re sitting together, with lots of real-life examples, warnings, and best practices. Let’s pretend we’re building an online bookstore database called bookshop. Ready? Let’s go!
1. CREATE TABLE – Building Your First Table
This is the command you use to create a new table and define its structure (columns, data types, constraints).
Basic Syntax:
|
0 1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE table_name ( column1 datatype [constraints], column2 datatype [constraints], ... ); |
Real-life example – A simple books table:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE books ( book_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID, auto-increases title VARCHAR(200) NOT NULL, -- Book title, cannot be empty author VARCHAR(100), -- Author name price DECIMAL(8,2), -- Price with 2 decimal places (e.g., 499.99) publication_year INT, -- Year published isbn VARCHAR(13) UNIQUE, -- ISBN must be unique is_available BOOLEAN DEFAULT TRUE -- In stock? Default is yes ); |
Explanation of each part:
- book_id INT AUTO_INCREMENT PRIMARY KEY → This is the primary key (unique identifier for each row). AUTO_INCREMENT means it automatically gives 1, 2, 3… every time you add a book.
- VARCHAR(200) → Variable-length string, max 200 characters
- NOT NULL → This column cannot be left empty
- DECIMAL(8,2) → Up to 8 digits total, 2 after decimal (perfect for money: ₹12,345.67)
- UNIQUE → No two books can have the same ISBN
- BOOLEAN DEFAULT TRUE → Yes/No field, defaults to true (available)
Even better version (with modern best practices):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE IF NOT EXISTS books ( book_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, author VARCHAR(150) NOT NULL, publisher VARCHAR(100), price DECIMAL(10,2) NOT NULL DEFAULT 0.00, publication_year SMALLINT UNSIGNED, isbn CHAR(13) UNIQUE, pages SMALLINT UNSIGNED, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, is_available TINYINT(1) DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
Why these extras?
- IF NOT EXISTS → Safe — won’t error if table already exists
- UNSIGNED → Only positive numbers (good for IDs, years)
- CHAR(13) → Fixed length for ISBN (always 13 characters)
- TIMESTAMP … ON UPDATE → Automatically records when row was created/updated
- ENGINE=InnoDB → Best storage engine for transactions (MySQL/PostgreSQL default)
- utf8mb4 → Supports emojis, Hindi, Marathi, etc.
2. DROP TABLE – Deleting a Table (Be Very Careful!)
This command completely deletes the table and all its data — no recycle bin!
Syntax:
|
0 1 2 3 4 5 6 |
DROP TABLE table_name; |
Safe version:
|
0 1 2 3 4 5 6 |
DROP TABLE IF EXISTS books; |
Example:
|
0 1 2 3 4 5 6 7 8 9 10 |
-- First check if it exists SHOW TABLES; -- Now delete the old test table DROP TABLE IF EXISTS old_books_backup; |
Warning: Just like DROP DATABASE, there is no UNDO. In production, many teams remove DROP TABLE permission from regular users — only DBAs can do it. Always think twice: “Do I really want to lose this data forever?”
3. ALTER TABLE – Modifying an Existing Table
Life changes — sometimes you need to add a new column, change a data type, rename something, or drop a column. ALTER TABLE does all that.
Common uses:
A. Add a new column
|
0 1 2 3 4 5 6 7 |
ALTER TABLE books ADD COLUMN stock_quantity INT UNSIGNED DEFAULT 0; |
B. Modify (change) a column
|
0 1 2 3 4 5 6 7 8 |
-- Change price to allow bigger amounts ALTER TABLE books MODIFY COLUMN price DECIMAL(12,2) NOT NULL DEFAULT 0.00; |
C. Rename a column
|
0 1 2 3 4 5 6 7 |
ALTER TABLE books RENAME COLUMN publication_year TO year_published; |
D. Drop (delete) a column
|
0 1 2 3 4 5 6 7 |
ALTER TABLE books DROP COLUMN pages; |
E. Add a foreign key (linking tables) – we’ll cover this more in constraints later
|
0 1 2 3 4 5 6 7 8 |
ALTER TABLE books ADD COLUMN author_id INT UNSIGNED, ADD FOREIGN KEY (author_id) REFERENCES authors(author_id); |
F. Change table name
|
0 1 2 3 4 5 6 7 |
ALTER TABLE books RENAME TO bookstore_books; |
Full example – Let’s improve our books table step by step:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- 1. Add stock and rating columns ALTER TABLE books ADD COLUMN stock_quantity INT UNSIGNED DEFAULT 0 AFTER isbn, ADD COLUMN average_rating DECIMAL(3,2) DEFAULT 0.00; -- 2. Make title longer ALTER TABLE books MODIFY COLUMN title VARCHAR(300) NOT NULL; -- 3. Add a created_by column for who added the book ALTER TABLE books ADD COLUMN created_by VARCHAR(50) DEFAULT 'admin'; |
4. TRUNCATE TABLE – Emptying a Table (Fast Delete All Rows)
TRUNCATE deletes all rows from a table but keeps the table structure (columns, indexes, constraints).
Syntax:
|
0 1 2 3 4 5 6 |
TRUNCATE TABLE table_name; |
Example:
|
0 1 2 3 4 5 6 7 8 9 10 |
-- Imagine we have 1 million test rows in books_test TRUNCATE TABLE books_test; -- Now the table is empty, but structure is still there -- AUTO_INCREMENT resets to 1 (unlike DELETE) |
TRUNCATE vs DELETE – Important Differences
| Feature | TRUNCATE TABLE | DELETE FROM table_name; |
|---|---|---|
| Deletes all rows? | Yes | Yes |
| Keeps table structure? | Yes | Yes |
| Resets AUTO_INCREMENT? | Yes (back to 1) | No (keeps current value) |
| Can use WHERE clause? | No | Yes |
| Triggers fire? | Usually no | Yes |
| Speed | Much faster (drops & recreates table internally) | Slower (logs every row) |
| Can rollback? | No (in most cases) | Yes (inside transaction) |
| Use when… | You want to completely reset data | You want to delete selectively |
Best Practice: Use TRUNCATE only when you’re sure you want to wipe everything and start fresh (e.g., resetting test data).
Full Workflow Example (Copy-Paste This!)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
-- 1. Create our main books table CREATE TABLE IF NOT EXISTS books ( book_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, author VARCHAR(150) NOT NULL, price DECIMAL(10,2) NOT NULL DEFAULT 0.00, isbn CHAR(13) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 2. Add more columns later ALTER TABLE books ADD COLUMN stock INT UNSIGNED DEFAULT 0, ADD COLUMN rating DECIMAL(3,2) DEFAULT NULL; -- 3. Oops – we don't need rating anymore ALTER TABLE books DROP COLUMN rating; -- 4. Fill with test data (we'll learn INSERT next chapter) -- ... imagine we inserted 100 rows ... -- 5. Want to start fresh? Empty it quickly TRUNCATE TABLE books; -- 6. If we hate it completely DROP TABLE IF EXISTS books; |
