Can I rename a column in MySQL?

Yes, use ALTER TABLE table_name RENAME COLUMN old_name TO new_name;. This requires MySQL 8.0 or later. In earlier versions, you had to use CHANGE with the full column definition. Renaming a column may break existing queries and applications.

What is the difference between DROP TABLE and TRUNCATE TABLE?

DROP TABLE removes the table definition and all its data permanently. The table no longer exists. TRUNCATE TABLE removes all rows but keeps the table structure intact. You can still INSERT new rows into a truncated table, but you cannot query a dropped table.

Modifying Schema

Change table structure with ALTER TABLE, DROP, and schema inspection commands.

Your first schema will not be perfect. Requirements change, new fields are needed, and old columns become obsolete. ALTER TABLE lets you evolve your schema without losing data. You will also learn to inspect your tables and remove ones you no longer need.

Definition

ALTER TABLE is a DDL statement that modifies an existing table's structure. You can add, modify, or drop columns, rename the table, and manage indexes without losing data.

Required step: If you do not have the school database and students table yet, run this setup in the playground:

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100)
);

INSERT INTO students (name, email)
VALUES
  ('Alice', '[email protected]'),
  ('Bob', '[email protected]'),
  ('Charlie', '[email protected]');

Troubleshooting: If you get "table doesn't exist" errors, re-run the setup above. It uses IF NOT EXISTS so it is safe to run multiple times. If the last example in this stage dropped the table, re-run the setup above before continuing.

Inspecting Tables

Before modifying a table, you need to see what columns it has. MySQL provides several ways to inspect your schema.

List tables in a database

SHOW TABLES;

Returns all tables in the current database. You should see 'students' listed.

Describe table structure

DESCRIBE students;

Shows each column's name, data type, whether it allows NULL, key info, default value, and extra info. DESCRIBE is shorthand for SHOW COLUMNS FROM.

Alternative syntax

SHOW COLUMNS FROM students;

Same result as DESCRIBE. SHOW COLUMNS is the explicit form of the command (DESCRIBE is shorthand for it).

Adding Columns

Use ALTER TABLE ... ADD COLUMN to add a new column to an existing table. The new column is added with a default value or as nullable.

Add a column

ALTER TABLE students
ADD COLUMN phone VARCHAR(20);

Adds a phone column to the students table. Since no DEFAULT is specified and no NOT NULL constraint, it allows NULL values.

Add a column with a default

ALTER TABLE students
ADD COLUMN status ENUM('active', 'inactive') NOT NULL DEFAULT 'active';

Adds a status column that defaults to 'active'. Existing rows get the default value.

Modifying Columns

Use MODIFY to change a column's data type, constraints, or default value. The column name stays the same.

Change column type

ALTER TABLE students
MODIFY COLUMN phone VARCHAR(25);

Widens the phone column from VARCHAR(20) to VARCHAR(25). This is safe even if data already exists.

Add NOT NULL to an existing column

ALTER TABLE students
MODIFY COLUMN phone VARCHAR(25) NOT NULL;

Adds a NOT NULL constraint. This fails if any existing rows have NULL in the phone column. Clean the data first.

Renaming and Dropping Columns

Rename a column

ALTER TABLE students
RENAME COLUMN phone TO telephone;

Changes the column name from phone to telephone. All existing queries referencing phone must be updated.

Drop a column

ALTER TABLE students
DROP COLUMN telephone;

Removes the column and all its data permanently. There is no undo without a backup.

Tip:Multiple Changes at Once

You can combine multiple ALTER operations in a single statement, separated by commas. This is faster than running separate ALTER TABLE statements because MySQL only rebuilds the table once.

ALTER TABLE students
  ADD COLUMN graduation_year INT,
  MODIFY COLUMN email VARCHAR(255) NOT NULL,
  DROP COLUMN status;

Dropping Tables and Databases

DROP permanently removes a table (including its structure) or an entire database. This cannot be undone.

Drop a table

DROP TABLE IF EXISTS old_students;

Removes old_students if it exists. IF EXISTS prevents an error if the table is already gone. All data in the table is lost.

Drop a database

DROP DATABASE IF EXISTS test_db;

Removes the entire database including all its tables and data. Use IF EXISTS to avoid errors.

Warning: DROP TABLE and DROP DATABASE are irreversible. Always back up important data before running these commands. Use IF EXISTS to prevent errors.

What does DESCRIBE students do?

You want to add a NOT NULL column to a table that already has rows. What must you do first?

Key Takeaways

SHOW TABLES and DESCRIBE let you inspect your schema before modifying it.
ALTER TABLE ... ADD/MODIFY/RENAME/DROP COLUMN changes table structure.
Multiple ALTER operations can be combined in one statement for efficiency.
DROP TABLE and DROP DATABASE are permanent. Always use IF EXISTS.
Adding NOT NULL without a DEFAULT to a table with existing rows will error in strict mode (the default). Provide an explicit DEFAULT or update existing rows first.

Ready to test your knowledge?

Take a Quiz