MySQL ALTER TABLE

MySQL ALTER TABLE Statement

The ALTER TABLE statement in MySQL is used to modify an existing table's structure. It allows you to add, delete, or modify columns, change table options, and manage constraints.

Basic Syntax

ALTER TABLE table_name action;

• table_name: Name of the table you want to modify.
• action: The specific change you want to make (e.g., adding a column, renaming a column, changing a data type).

Common Uses

1. Add a Column

Add a new column to a table:

ALTER TABLE table_name 
ADD column_name column_definition;

Example:

ALTER TABLE employees 
ADD age INT;

2. Drop a Column

Remove a column from a table:

ALTER TABLE table_name 
DROP COLUMN column_name;

Example:

ALTER TABLE employees 
DROP COLUMN age;

3. Modify a Column

Change a column’s data type, size, or attributes:

ALTER TABLE table_name 
MODIFY column_name new_definition;

Example:

ALTER TABLE employees 
MODIFY age SMALLINT;

4. Rename a Column

Rename a column using CHANGE:

ALTER TABLE table_name 
CHANGE old_column_name new_column_name column_definition;

Example:

ALTER TABLE employees 
CHANGE age employee_age INT;

5. Rename a Table

Change the name of a table:

ALTER TABLE old_table_name 
RENAME TO new_table_name;

Example:

ALTER TABLE employees 
RENAME TO staff;

6. Add a Primary Key

Add a primary key to a table:

ALTER TABLE table_name 
ADD PRIMARY KEY (column_name);

Example:

ALTER TABLE employees 
ADD PRIMARY KEY (id);

7. Drop a Primary Key

Remove the primary key constraint:

ALTER TABLE table_name 
DROP PRIMARY KEY;

Example:

ALTER TABLE employees 
DROP PRIMARY KEY;

8. Add a Foreign Key

Add a foreign key constraint:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name 
FOREIGN KEY (column_name) REFERENCES other_table (column_name);

Example:

ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) REFERENCES customers (id);

9. Drop a Foreign Key

Remove a foreign key constraint:

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;

Example:

ALTER TABLE orders 
DROP FOREIGN KEY fk_customer;

10. Add an Index

Add an index to a column:

ALTER TABLE table_name 
ADD INDEX index_name (column_name);

Example:

ALTER TABLE employees 
ADD INDEX idx_name (name);

11. Drop an Index

Remove an index from a column:

ALTER TABLE table_name 
DROP INDEX index_name;

Example:

ALTER TABLE employees 
DROP INDEX idx_name;

Multiple Actions in One Statement

You can perform multiple modifications in a single ALTER TABLE statement:

ALTER TABLE employees 
ADD salary DECIMAL(10, 2), 
DROP COLUMN age, 
MODIFY name VARCHAR(100);

Best Practices

1. Backup Data: Always back up your database before altering tables.
2. Test Changes: Test ALTER TABLE commands on a development or staging database first.
3. Avoid Downtime: For large tables, use tools like pt-online-schema-change to avoid locking the table.

Common Errors and Solutions

1. Error: Cannot drop a column referenced by a foreign key constraint.

   • Solution: Drop the foreign key constraint before dropping the column.

2. Error: Data type mismatch.

   • Solution: Ensure the new data type is compatible with existing data.

3. Error: Duplicate key error when adding a primary key.

   • Solution: Ensure all rows have unique values in the column you are making the primary key.

Let me know if you need help with specific examples or advanced usage!