MySQL : ALTER TABLE Statement

The ALTER TABLE statement in MySQL is used to modify an existing table. It allows you to add, modify, or drop columns, add or remove indexes, modify the table's engine, and perform other structural changes to the table.

Here's the basic syntax for various alterations:

1. Add a new column:

   ALTER TABLE table_name
   ADD column_name datatype [optional_constraints];
   

2. Modify an existing column:

   
   ALTER TABLE table_name
   MODIFY column_name datatype [optional_constraints];
 

3. Drop a column:

   ALTER TABLE table_name
   DROP COLUMN column_name;
   

4. Add a primary key:

   
   ALTER TABLE table_name
   ADD PRIMARY KEY (column_name);
 

5. Add a foreign key:

   ALTER TABLE table_name
   ADD CONSTRAINT fk_constraint_name FOREIGN KEY (column_name)
   REFERENCES parent_table (parent_column);
 

6. Drop a constraint:

  
   ALTER TABLE table_name
   DROP FOREIGN KEY fk_constraint_name;
 

7. Add an index:

   ALTER TABLE table_name
   ADD INDEX index_name (column_name);
 

8. Drop an index:

 
   ALTER TABLE table_name
   DROP INDEX index_name;
 

These are just a few examples of what you can do with the ALTER TABLE statement. It's a powerful tool for making changes to your database schema after the initial table creation. Be cautious when altering tables, especially if the table contains data, as structural changes may affect existing data or queries. Always back up your data before making significant alterations.