MySQL ALTER Command

When we need to modify a table name or table fields, we need to use the MySQLALTERcommand.

The MySQLALTERcommand is used to modify the structure of objects such as databases, tables, and indexes.

ALTERThe command allows you to add, modify, or delete database objects, and can be used for operations such as changing table column definitions, adding constraints, and creating and deleting indexes.

The ALTER command is very powerful and can flexibly modify and adjust when the database structure changes.

The following areALTERcommon usages and examples of the command:

1. Add Column

ALTER TABLE table_name
ADD COLUMN new_column_name datatype;

The following SQL statement adds a date column named birth_date to the employees table:

2. Modify Column Data Type

The following SQL statement changes the data type of the salary column in the employees table to DECIMAL(10,2):

3. Modify Column Name

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype;

The following SQL statement changes a column name in the employees table from old_column_name to new_column_name, and can also modify the data type at the same time:

4. Delete Column

ALTER TABLE table_name
DROP COLUMN column_name;

The following SQL statement deletes the birth_date column from the employees table:

5. Add PRIMARY KEY

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

The following SQL statement adds a primary key to the employees table:

6. Add FOREIGN KEY

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES parent_table (column_name);

The following SQL statement adds a foreign key to the orders table, referencing the customer_id column of the customers table:

7. Modify Table Name

ALTER TABLE old_table_name
RENAME TO new_table_name;

The following SQL statement changes the table name from employees to staff:

Note:

But when using theALTERcommand, you must be extra careful, because some operations may require rebuilding tables or indexes, which can affect database performance and runtime.

When making important structural modifications, it is recommended to back up data first and operate cautiously in the production environment.

Example

Before starting this chapter's tutorial, let's first create a table named:testalter_tbl。

Delete, Add, or Modify Table Fields

The following command uses the ALTER command and the DROP clause to delete the i field from the table created above:

mysql> ALTER TABLE testalter_tbl  DROP i;

If only one field remains in the data table, DROP cannot be used to delete the field.

In MySQL, use the ADD clause to add a column to a data table. The following example adds an i field to the table testalter_tbl and defines the data type:

mysql> ALTER TABLE testalter_tbl ADD i INT;

After executing the above command, the i field will be automatically added to the end of the data table fields.

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

If you need to specify the position of the new field, you can use the keywords provided by MySQL: FIRST (to set it as the first column), AFTER field name (to set it after a certain field).

Try the following ALTER TABLE statement. After successful execution, use SHOW COLUMNS to view the changes in the table structure:

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

The FIRST and AFTER keywords can be used with the ADD and MODIFY clauses, so if you want to reset the position of a data table field, you need to first use DROP to delete the field, then use ADD to add the field and set its position.

Modify Field Type and Name

If you need to modify the field type and name, you can use the MODIFY or CHANGE clause in the ALTER command.

For example, to change the type of field c from CHAR(1) to CHAR(10), you can execute the following command:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

Using the CHANGE clause has quite different syntax. After the CHANGE keyword, the field name you want to modify follows immediately, then specify the new field name and type. Try the following example:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Changing a Table Type:

mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
*************************** 1. row ****************
           Name: testalter_tbl
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 25769803775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-06-03 08:04:36
    Update_time: 2007-06-03 08:04:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;