YouTipMysql Alter
When we need to modify a table name or alter table fields, we need to use the MySQL ALTER command.
The MySQL ALTER command is used to modify the structure of database objects such as databases, tables, and indexes.
The ALTER command allows you to add, modify, or delete database objects, and can be used to change column definitions, add constraints, create and delete indexes, and more.
The ALTER command is very powerful, allowing for flexible modifications and adjustments when the database structure changes.
Here are common usages and examples of the ALTER command:
### 1. Adding a 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:
## Example
ALTER TABLE employees
ADD COLUMN birth_date DATE;
### 2. Modifying a Column's Data Type
## Example
ALTER TABLE TABLE_NAME
MODIFY COLUMN column_name new_datatype;
The following SQL statement changes the data type of the salary column in the employees table to DECIMAL(10,2):
## Example
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(10,2);
### 3. Renaming a Column
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name datatype;
The following SQL statement renames a column in the employees table from old_column_name to new_column_name, and can also change the data type simultaneously:
## Example
ALTER TABLE employees
CHANGE COLUMN old_column_name new_column_name VARCHAR(255);
### 4. Deleting a Column
ALTER TABLE table_name DROP COLUMN column_name;
The following SQL statement deletes the birth_date column from the employees table:
## Example
ALTER TABLE employees
DROP COLUMN birth_date;
### 5. Adding a PRIMARY KEY
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
The following SQL statement adds a primary key to the employees table:
## Example
ALTER TABLE employees
ADD PRIMARY KEY(employee_id);
### 6. Adding a 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 in the customers table:
## Example
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY(customer_id)
REFERENCES customers (customer_id);
### 7. Renaming a Table
ALTER TABLE old_table_name RENAME TO new_table_name;
The following SQL statement renames the table from employees to staff:
## Example
ALTER TABLE employees
RENAME TO staff;
> **Note:**
>
>
> However, be extremely careful when using the ALTER command, as some operations may require rebuilding the table or indexes, which can impact database performance and runtime.
>
>
> Before making important structural changes, it is recommended to back up your data first and proceed with caution in a production environment.
* * *
## Example
Before starting this chapter's tutorial, let's first create a table named: **testalter_tbl**.
## Example
root@host# mysql -u root -p password;
Enter password: *******
mysql>USE ;
DATABASE changed
mysql>CREATE TABLE testalter_tbl
->(
-> i INT,
-> c CHAR(1)
->);
Query OK,0 ROWS affected (0.05 sec)
mysql>SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
|FIELD|TYPE|NULL|KEY|DEFAULT| Extra |
+-------+---------+------+-----+---------+-------+
| i |INT(11)| YES ||NULL||
| c |CHAR(1)| YES ||NULL||
+-------+---------+------+-----+---------+-------+
2 ROWS IN SET(0.00 sec)
### Deleting, Adding, or Modifying Table Fields
The following command uses the ALTER command with the DROP clause to delete the i field created above:
mysql> ALTER TABLE testalter_tbl DROP i;
If a table only has one field left, you cannot use DROP to delete that field.
In MySQL, use the ADD clause to add a column to a table. The following example adds the i field to the testalter_tbl table and defines its 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 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 FIRST (to set it as the first column) or AFTER field_name (to set it after a specific field) provided by MySQL.
Try the following ALTER TABLE statements. After successful execution, use SHOW COLUMNS to see 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 table field, you need to first use DROP to delete the field and then use ADD to add it back with the desired position.
### Modifying Field Type and Name
If you need to modify a field's type and name, you can use the MODIFY or CHANGE clauses 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 a very different syntax. After the CHANGE keyword, you specify the field name you want to modify, followed by the new field name and its type. Try the following example:
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
### ALTER TABLE's Impact on Null Values and Default Values
When modifying a field, you can specify whether it contains a value or has a default value set.
The following example specifies field j as NOT NULL with a default value of 100.
mysql> ALTER TABLE testalter_tbl -> MODIFY j BIGINT NOT NULL DEFAULT 100;
If you do not set a default value, MySQL will automatically set the field's default to NULL.
### Modifying a Field's Default Value
You can use ALTER to modify a field's default value. Try the following example:
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)
You can also use the ALTER command with the DROP clause to delete a field's default value, as shown in the following example:
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:
To modify a table's type, you can use the ALTER command with the ENGINE clause. Try the following example, where we change the type of the testalter_tbl table to MYISAM:
**Note:** You can use the SHOW TABLE STATUS statement to view a table's 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: 0Max_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)
### Renaming a Table
If you need to change a table's name, you can use the RENAME clause in the ALTER TABLE statement.
Try the following example to rename the testalter_tbl table to alter_tbl:
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
The ALTER command can also be used to create and delete indexes on MySQL tables, a feature we will introduce in the following chapters.