The ALTER TABLE statement in MySQL provides various operations to modify the structure of an existing table.

These operations allow database administrators and developers to adapt table structures to changing requirements without needing to rebuild the entire table from scratch.

✅Setting up a sample table

For demonstration, let’s use a realistic students table:

CREATE TABLE students (
    student_id INT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    email VARCHAR(100),
    enrollment_year INT NOT NULL,
    PRIMARY KEY(student_id)
);

This statement shows the column list of the students table:

DESCRIBE students;

9. DROP INDEX

Purpose:

Remove an existing index.

ALTER TABLE table_name
DROP INDEX index_name;

Example of Drop idx_make

ALTER TABLE vehicles
DROP INDEX idx_make;

10. ALTER COLUMN DEFAULT

Purpose:

Add, change, or remove the default value for a column.

Syntax: Set a default

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

Example: Set default year to 2025:

ALTER TABLE vehicles
ALTER COLUMN year SET DEFAULT 2025;

Syntax: Drop default

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

11. MODIFY TABLE PROPERTIES

Purpose:

Change storage engine, character set, or collation. Syntax:

ALTER TABLE table_name
ENGINE = InnoDB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

12. PARTITION OPERATIONS

Purpose:

Add, reorganize, or remove table partitions (for large tables).

🔹 Syntax: Add partition (basic example)

ALTER TABLE table_name
PARTITION BY RANGE (year) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

✅ Good Practice Tips

1. Always back up your table before performing structural changes.
2. Use DESCRIBE table_name or SHOW CREATE TABLE table_name to verify changes.
3. Use constraints and indexes wisely to balance data integrity and performance.
4. Test changes in a staging environment when possible.

🗂️ Summary

The ALTER TABLE command is a powerful tool for evolving your database schema without dropping and recreating tables. Mastering these categories will make you a more flexible and reliable database developer or administrator.

Let me know if you want a complete practical workbook with exercises for each!