12 - MySQL Alter

MySQL alter command is used to change the schema of the tables mean changing the definition of tables. It allows:

  • Adding a new column in existing table.
  • Removing a column from existing table.
  • Modifying the data type, name, default values of a column in the existing table
  • Creating new index or constraint in existing table.
  • Dropping the index from the table.
  • Renaming the existing table.
  • Changing the Engine of the Table.
  • Changing the collation or charset of table or some columns.

ALTER command is AUTO COMMIT command. Basic syntax of ALTER command is shown below, actions here contain any operations listed above:

ALTER TABLE TBALE_NAME [ACTIONS]

Adding a New Column in Existing Table

For example we have a data of the company names along with their address and price of some server.

ID

Company

Address

Amount

1

HCL

NY

$1000

2

Apple

Chicago

$2000

3

IBM

INDIA

$3000

4

Google

Singapore

$15000

Now if we want to add the column name year_of_manufacturing (YOM), then ALTER command is:
      ALTER TABLE company ADD COLUMN YOM YEAR DEFAULT ‘1999’;
Here company is the table name, YOM is new column name, default value is 1999 and YEAR is the data type.

After Alteration of the table, structure will be:

ID

Company

Address

Amount

YOM

1

HCL

NY

$1000

1999

2

Apple

Chicago

$2000

1999

3

IBM

INDIA

$3000

1999

4

Google

Singapore

$15000

1999

Note: After adding the column there is no data or default values if you defined in the command.

Removing a column from the existing table.

Suppose we don’t want the Amount information from the table then the alter command to drop the AMOUNT column is written as:

            ALTER TABLE Company DROP Column Amount;

ID

Company

Address

YOM

1

HCL

NY

1999

2

Apple

Chicago

1999

3

IBM

INDIA

1999

4

Google

Singapore

1999

Note: The result of alter command always shows the number of rows affected which is same as the total number of rows in the table. For example
mysql>
mysql> ALTER TABLE cities ADD COLUMN YOM YEAR ;

Query OK, 4 rows affected (1.81 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql>

Modifying the data type, name, default values of a column in the existing table

Now suppose we want to change the YOM column to some date/month/year format and the default value of which is 0000-00-00 and then column name is date-of-manufacturing DOM. Then the ALTER command will be:

ALTER table Company MODIFY COLUMN YOM DOM DATE Default ‘0000-00-00’;

After altering the table the table data will be:

ID

Company

Address

Amount

DOM

1

HCL

NY

$1000

0000-00-00

2

Apple

Chicago

$2000

0000-00-00

3

IBM

INDIA

$3000

0000-00-00

4

Google

Singapore

$15000

0000-00-00

Here the name and the values of the column both are changed. Also the data type YEAR is changed to DATE to accommodate the month and date in this column.

Create new index or constraint in existing table.

For Adding the index in the table on a particular column say Company the ALTER query is:

      Alter Table Company ADD INDEX `idx_1`(`Company`);  

Here the index name idx_1 is created over the column Company. There is no change in the name, values, data type and default values of the column.

Drop the index from the table.

For drop the index from the table we can use the ALTER command as:

     Alter Table Company DROP INDEX `idx_1`;

Here we don’t need to define the column name as the index name is uniquely defined in the table definition. No effect on name, values, data type and default values of the column.

Rename the existing table.

For changing the table name we can use the ALTER command as:

     Alter Table Company RENAME TO `Company_data`;

Here “RENAME TO“ is the keyword for changing the table name via ALTER command. No effect on name, values, data type and default values of the column.

Change the Engine of the Table.

For change the Storage engine of the table we can use the following ALTER command:   

      Alter Table Company_dataENGINE=’InnoDB’;

If the engine is already InnoDB then there is no effect on the table, otherwise the engine changed to InnoDB as defined in the ALTER command.

Change the collation or charset of table or some columns.

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. 

For example: To change the collation of the table.

    Alter table Company convertto character set utf8 collate utf8_unicode_ci;

Here Company is the table name and utf8 is the new character set and utf8_unicode_ci is new Collation.

Like us on Facebook