Besides creating table, MySQL allows you to alter existing table structures with a lot of options. Here is the ALTER TABLE statement:
ALTER [IGNORE] TABLE table_name options[, options…] options:
ADD [COLUMN] create_definition [FIRST | AFTER col_name ] or ADD [COLUMN] (create_definition, create_definition,…)
or ADD INDEX [index_name] (index_col_name,…)
or ADD PRIMARY KEY (index_col_name,…)
or ADD UNIQUE [index_name] (index_col_name,…)
or ADD FULLTEXT [index_name] (index_col_name,…)
or ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,…)
[reference_definition] or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name] or MODIFY [COLUMN] create_definition [FIRST | AFTER col_name] or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or DISABLE KEYS
or ENABLE KEYS
or RENAME [TO] new_table_name
or ORDER BY col_name
or table_options
Most of these options are obvious. We will explain some here:
- The CHANGE and MODIFY are the same, they allow you to change the definition of the column or its position in the table.
- The DROP COLUMN will drop the column of the table permanently, if the table contains data, all the data of that column will be lost.
- The DROP PRIMARY KEY and DROP INDEX only remove the primary key or index of the column.
- The RENAME Clause allows you the change the table name to the new one.
Eg:
In this example, we add a new column ‘college’ to our table ‘student’. The column will be added at the end of the table. You can also specify the position of the column using the ‘after’ keyword.
ALTER TABLE student ADD COLUMN college VARCHAR(30);
