Altering and Dropping Tables: Modifying Database Structure

Databases evolve over time—requirements change, new features are added, and old structures need updates. ALTER TABLE and DROP TABLE are the DDL commands that let you modify and remove database objects after creation. In this lesson, you'll learn how to add, modify, and remove columns; add and drop constraints; rename tables; and safely delete tables when they're no longer needed. Mastering these commands is essential for maintaining and evolving production databases.

📚 What You'll Learn

• How to add new columns to existing tables
• How to modify column definitions (data type, constraints)
• How to remove columns safely
• How to rename columns and tables
• How to add and drop constraints
• How to delete tables with DROP TABLE
• The difference between DROP, TRUNCATE, and DELETE
• Best practices for altering production databases
• How to avoid data loss during schema changes

1. ALTER TABLE: Modifying Existing Tables

Common ALTER TABLE Operations

2. Adding Columns to Existing Tables

Basic Syntax

ALTER TABLE table_name
ADD COLUMN column_name datatype constraints;

Examples: Adding Columns

-- Add a simple column
ALTER TABLE customers
ADD COLUMN phone VARCHAR(20);

-- Add column with NOT NULL and default
ALTER TABLE products
ADD COLUMN is_featured BOOLEAN NOT NULL DEFAULT FALSE;

-- Add column with position (after specific column)
ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(100) AFTER first_name;

-- Add multiple columns at once
ALTER TABLE orders
ADD COLUMN shipping_address TEXT,
ADD COLUMN billing_address TEXT,
ADD COLUMN notes TEXT;

Adding Columns with Existing Data

-- ❌ This fails if table has data
ALTER TABLE customers
ADD COLUMN loyalty_points INT NOT NULL;
-- ERROR: Cannot add NOT NULL column without default

-- ✅ This works - provides default for existing rows
ALTER TABLE customers
ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0;

-- ✅ Alternative: Make it nullable
ALTER TABLE customers
ADD COLUMN loyalty_points INT;

3. Modifying Existing Columns

Basic Syntax

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype new_constraints;

Examples: Modifying Columns

-- Change data type (make column larger)
ALTER TABLE products
MODIFY COLUMN description TEXT;  -- was VARCHAR(255)

-- Add NOT NULL constraint
ALTER TABLE customers
MODIFY COLUMN email VARCHAR(255) NOT NULL;

-- Remove NOT NULL constraint (make nullable)
ALTER TABLE employees
MODIFY COLUMN middle_name VARCHAR(100) NULL;

-- Change data type and add constraint
ALTER TABLE orders
MODIFY COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';

Safe vs. Risky Modifications

4. Removing Columns

Basic Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Examples: Dropping Columns

-- Drop a single column
ALTER TABLE customers
DROP COLUMN fax_number;

-- Drop multiple columns
ALTER TABLE products
DROP COLUMN old_price,
DROP COLUMN legacy_id;

What You Cannot Drop

• ❌ Primary key columns (must drop PK constraint first)
• ❌ Columns referenced by foreign keys (must drop FK first)
• ❌ The last remaining column in a table

-- ❌ This fails - column is a foreign key reference
ALTER TABLE orders
DROP COLUMN customer_id;
-- ERROR: Cannot drop column referenced by foreign key

-- ✅ Solution: Drop foreign key first, then column
ALTER TABLE orders
DROP FOREIGN KEY fk_order_customer;

ALTER TABLE orders
DROP COLUMN customer_id;

5. Renaming Columns and Tables

Rename Column

-- Rename a column
ALTER TABLE customers
RENAME COLUMN old_name TO new_name;

-- Example: Fix a typo
ALTER TABLE products
RENAME COLUMN prodcut_name TO product_name;

-- Example: Better naming
ALTER TABLE employees
RENAME COLUMN emp_id TO employee_id;

Rename Table

-- Rename a table
RENAME TABLE old_table_name TO new_table_name;

-- Example
RENAME TABLE customer TO customers;

-- Rename multiple tables at once
RENAME TABLE 
    old_customers TO customers,
    old_orders TO orders;

6. Managing Constraints

Adding Constraints

-- Add PRIMARY KEY
ALTER TABLE students
ADD PRIMARY KEY (student_id);

-- Add UNIQUE constraint
ALTER TABLE users
ADD CONSTRAINT uq_email UNIQUE (email);

-- Add FOREIGN KEY
ALTER TABLE orders
ADD CONSTRAINT fk_order_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- Add CHECK constraint
ALTER TABLE products
ADD CONSTRAINT chk_positive_price CHECK (price > 0);

-- Add multiple constraints
ALTER TABLE employees
ADD CONSTRAINT uq_employee_email UNIQUE (email),
ADD CONSTRAINT chk_valid_salary CHECK (salary >= 0);

Dropping Constraints

-- Drop PRIMARY KEY
ALTER TABLE students
DROP PRIMARY KEY;

-- Drop UNIQUE constraint
ALTER TABLE users
DROP INDEX uq_email;

-- Drop FOREIGN KEY
ALTER TABLE orders
DROP FOREIGN KEY fk_order_customer;

-- Drop CHECK constraint
ALTER TABLE products
DROP CHECK chk_positive_price;

7. Dropping Tables

Basic Syntax

DROP TABLE table_name;

Examples

-- Drop a single table
DROP TABLE temp_data;

-- Drop table if it exists (no error if missing)
DROP TABLE IF EXISTS old_table;

-- Drop multiple tables
DROP TABLE table1, table2, table3;

What Happens When You Drop a Table

• ✓ Table structure is deleted
• ✓ All data is deleted
• ✓ All indexes are deleted
• ✓ All triggers are deleted
• ❌ Foreign keys in other tables referencing this table will cause errors

8. DROP vs. TRUNCATE vs. DELETE

Examples

-- DROP TABLE - Remove table completely
DROP TABLE old_logs;

-- TRUNCATE TABLE - Delete all data, keep structure
TRUNCATE TABLE session_logs;

-- DELETE FROM - Remove specific rows
DELETE FROM users WHERE last_login < '2020-01-01';

9. Best Practices for Altering Tables

10. 🎯 Practice Exercises

-- Step 1: Add weight column (nullable, so no default needed)
ALTER TABLE products
ADD COLUMN weight DECIMAL(8, 2);

-- Step 2: Add is_discontinued (NOT NULL requires DEFAULT)
ALTER TABLE products
ADD COLUMN is_discontinued BOOLEAN NOT NULL DEFAULT FALSE;

-- Step 3: Modify description to larger type
ALTER TABLE products
MODIFY COLUMN description TEXT;

-- Step 4: Add CHECK constraint for price
ALTER TABLE products
ADD CONSTRAINT chk_positive_price CHECK (price > 0);

-- Verify changes
DESCRIBE products;

-- Step 1: Rename table
RENAME TABLE customer TO customers;

-- Step 2: Rename columns
ALTER TABLE customers
RENAME COLUMN cust_nm TO customer_name;

ALTER TABLE customers
RENAME COLUMN e_mail TO email;

ALTER TABLE customers
RENAME COLUMN ph TO phone;

-- Alternative: Rename multiple columns in one statement (MySQL 8.0+)
ALTER TABLE customers
RENAME COLUMN cust_nm TO customer_name,
RENAME COLUMN e_mail TO email,
RENAME COLUMN ph TO phone;

-- Verify changes
DESCRIBE customers;

-- Step 1: Add UNIQUE constraints
ALTER TABLE employees
ADD CONSTRAINT uq_employee_email UNIQUE (email);

ALTER TABLE employees
ADD CONSTRAINT uq_employee_number UNIQUE (employee_number);

-- Step 2: Add CHECK constraint for salary
ALTER TABLE employees
ADD CONSTRAINT chk_salary_range 
    CHECK (salary BETWEEN 30000 AND 500000);

-- Step 3: Add CHECK constraint for hire_date
ALTER TABLE employees
ADD CONSTRAINT chk_hire_date_not_future 
    CHECK (hire_date <= CURRENT_DATE);

-- Step 4: Add foreign key
ALTER TABLE employees
ADD CONSTRAINT fk_employee_department
    FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- Verify constraints
SHOW CREATE TABLE employees;

-- Check for duplicate emails
SELECT email, COUNT(*) 
FROM employees 
GROUP BY email 
HAVING COUNT(*) > 1;

-- Check for invalid salaries
SELECT * FROM employees 
WHERE salary < 30000 OR salary > 500000;

-- Check for invalid department_ids
SELECT * FROM employees 
WHERE department_id NOT IN (SELECT department_id FROM departments);

-- STEP 1: Add new columns
ALTER TABLE users
ADD COLUMN first_name VARCHAR(100) NOT NULL DEFAULT 'Unknown',
ADD COLUMN last_name VARCHAR(100) NOT NULL DEFAULT 'Unknown',
ADD COLUMN email VARCHAR(255) NOT NULL DEFAULT 'pending@example.com',
ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT FALSE,
ADD COLUMN last_login TIMESTAMP NULL,
ADD COLUMN account_status ENUM('active', 'suspended', 'deleted') 
    NOT NULL DEFAULT 'active';

-- STEP 2: Make email unique (after populating with real data)
-- First, you'd populate real emails in your application
-- Then add the unique constraint:
ALTER TABLE users
ADD CONSTRAINT uq_user_email UNIQUE (email);

-- STEP 3: Rename password column
ALTER TABLE users
RENAME COLUMN password TO password_hash;

-- STEP 4: Add CHECK constraint for username
ALTER TABLE users
ADD CONSTRAINT chk_username_length CHECK (LENGTH(username) >= 3);

-- STEP 5: In production, you'd migrate username data to first_name/last_name
-- For example:
-- UPDATE users SET 
--   first_name = SUBSTRING_INDEX(username, ' ', 1),
--   last_name = SUBSTRING_INDEX(username, ' ', -1);

-- STEP 6: After verifying data migration, drop username
-- (In production, wait several weeks to ensure no issues)
ALTER TABLE users
DROP COLUMN username;

-- Final structure verification
DESCRIBE users;
SHOW CREATE TABLE users;

Week 1: Add new columns with defaults
Week 2: Deploy app code using BOTH old and new columns
Week 3: Migrate data from username to first_name/last_name
Week 4: Switch app to use only new columns
Week 5-6: Monitor for issues
Week 7: Drop old username column

📝 Key Takeaways

• ALTER TABLE modifies existing table structure without losing data
• Use ADD COLUMN to add new fields to tables
• Use MODIFY COLUMN to change data types and constraints
• Use DROP COLUMN to remove unwanted columns (permanent!)
• Use RENAME to fix naming issues
• DROP TABLE permanently deletes table and all data
• TRUNCATE deletes data but keeps table structure
• DELETE removes specific rows with WHERE clause
• Always backup before ALTER operations
• Test schema changes on development copies first
• Adding NOT NULL requires DEFAULT for tables with data
• Adding constraints can fail if data violates them
• Dropping columns/tables is irreversible - be certain!
• Schema changes affect application code - coordinate updates
• Use IF EXISTS to avoid errors when dropping