ALTER TABLE

Status

Done

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.

The main categories are:

ADD COLUMN: Add new columns
MODIFY COLUMN: Change data type, constraints, or position
CHANGE/RENAME COLUMN: Rename a column
DROP COLUMN: Remove columns
RENAME TABLE: Change the table name
ADD CONSTRAINT: Add PRIMARY KEY, FOREIGN KEY, UNIQUE, or CHECK constraints
DROP CONSTRAINT: Remove constraints
ADD INDEX: Create new indexes
DROP INDEX: Remove indexes
ALTER COLUMN DEFAULT: Set or drop default values
MODIFY TABLE PROPERTIES: Change storage engine, charset, collation
PARTITION OPERATIONS: Add, reorganize, or remove partitions

✅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;

Field            Type          Null  Key  Default  Extra  
---------------  ------------  ----  ---  -------  -----  
student_id       int           NO    PRI  (null)          
first_name       varchar(50)   NO         (null)          
last_name        varchar(50)   NO         (null)          
date_of_birth    date          YES        (null)          
email            varchar(100)  YES        (null)          
enrollment_year  int           NO         (null)

‣

1. ADD COLUMN

‣

2. MODIFY COLUMNS

‣

3. CHANGE/RENAME A COLUMN

‣

4. DROP A COLUMN

‣

5. RENAME A TABLE

‣

6. ADD CONSTRAINT

‣

7. DROP CONSTRAINT

‣

8. ADD INDEX

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

Always back up your table before performing structural changes.
Use DESCRIBE table_name or SHOW CREATE TABLE table_name to verify changes.
Use constraints and indexes wisely to balance data integrity and performance.
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!