ALTER TABLE

mysql

How can we add a column or multiple columns?

ALTER TABLE vendor ADD COLUMN phone CHAR(20);
ALTER TABLE vendor ADD phone CHAR(20) NOT NULL;
ALTER TABLE Users 
    ADD COLUMN PhoneNotification TINYINT DEFAULT 1, 
    ADD COLUMN EmailNotification TINYINT DEFAULT 1, 
    ADD COLUMN AnnouncementsNotification TINYINT DEFAULT 1;

How can we remove / drop column:

ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, DROP COLUMN column_name3;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP column_name;

How can we rename column?

ALTER TABLE xyz CHANGE manufacurerid manufacturerid INT
ALTER TABLE xyz CHANGE manufacurerid manufacturerid

How can we modify column definition?

ALTER TABLE HeadOfState MODIFY ID BIGINT UNSIGNED NOT NULL;
ALTER TABLE HeadOfState CHANGE LastName LastName CHAR(40) NOT NULL;

The first way to alter a column definition is to use the MODIFY clause. You must specify the name of the column that you want to change, followed by its new definition:

ALTER TABLE HeadOfState MODIFY ID BIGINT UNSIGNED NOT NULL;

The new column definition must include all attributes that you wish to retain, because attributes are not carried over from existing definition.

The second way to alter a column definition is to use a CHANGE clause. The CHANGE clause enables you to modify both the column's definition and its name. To use this clause, specify the CHANGE keyword, followed by the existing column name, then its new name, and its new definition. This means, you must specify the existing column name twice if you want to change only the column definition. For example, to change the LastName column from CHAR(30) to CHAR(40) without renaming the column:

ALTER TABLE HeadOfState CHANGE LastName LastName CHAR(40) NOT NULL;

To change the name as well, provide the new name following the existing name:

ALTER TABLE HeadOfState CHANGE LastName Surname CHAR(40) NOT NULL;

How can we rename a table:

ALTER TABLE t1 RENAME TO t2;
RENAME TABLE t1 TO t2

RENAME TABLE has advantage over ALTER TABLE in that it can perform multiple table renames in a single operation. One use for this feature is to swap the names of two tables:

RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2;

For TEMPORARY tables, RENAME TABLE does not work. You must use ALTER TABLE instead.

How can we change the storage engine for a table?

ALTER TABLE table_name ENGINE = MyISAM;
ALTER TABLE table_name ENGINE = InnoDB;

How to add a column at a specific position?

When you add a new column to a table, MySQL places it after all existing columns. This is the default placement unless you specify otherwise. To indicate that MySQL should place the new column in a specific position within the table, append either the keyword FIRST or the keyword-identifier combination AFTER column_name to the column definition:

ALTER TABLE HeadOfState ADD InaugurationDate DATE NOT NULL FIRST;
ALTER TABLE HeadOfState ADD InaugurationDate DATE NOT NULL AFTER FirstName;

The FIRST keyword tells ALTER TABLE to place the new column before all existing columns (in the first position). The AFTER keyword tells ALTER TABLE to place the new column after a specific existing column. For example, to place the new Inauguration column after the existing FirstName column:

ALTER TABLE HeadOfState ADD Inauguration DATE NOT NULL AFTER FirstName

How can we specify multiple alterations for a table with a single ALTER TABLE statement?

We can specify multiple alterations for a table with a single ALTER TABLE statement. Just separate the actions by commas:

ALTER TABLE HeadOfState 
  RENAME TO CountryLeader, 
  MODIFY ID BIGINT UNSIGNED NOT NULL, 
  ADD Salutation CHAR(30) NULL AFTER FirstName;

How can we use ALTER TABLE to add an index to an existing table?

To add an index to a table with ALTER TABLE, use the ADD keyword followed by the index-type keywords and a list of columns to be indexed in parentheses:

ALTER TABLE HeadOfState ADD PRIMARY KEY (ID);
ALTER TABLE HeadOfState ADD INDEX (LastName, FirstName);

MySQL allows multiple actions to be performed with a single ALTER TABLE statement. One common use for multiple actions is to add several indexes to a table at the same time, which is more efficient than adding each one separately. Thus, the preceding two ALTER TABLE statements can be combined as follows:

ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName, FirstName);

How can we specify an indexing algorithm when using ALTER TABLE to add an index to an existing table?

ALTER TABLE lookup ADD INDEX USING BTREE (id);
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License