MariaDB table change command

The ALTER command provides a way to change the structure of an existing table, which means deleting or adding columns, modifying the index, changing the data type, changing the name, and so on. A LTER also waits for the app to change while the metadata lock is active.

Use ALTER to modify the column

ALTER pairing with DROP deletes the existing column. H owever, if the column is the only remaining column, it will fail.

See the example given below -

mysql> ALTER TABLE products_tbl DROP version_num;

Using ALTER ... A DD statement add column -

mysql> ALTER TABLE products_tbl ADD discontinued CHAR(1);

Specify the location of columns using the keywords FIRST andAFTER -

ALTER TABLE products_tbl ADD discontinued CHAR(1) FIRST;
ALTER TABLE products_tbl ADD discontinued CHAR(1) AFTER quantity;

Note THAT andAFTER keywords apply only to ALTER ... A DD statement. I n addition, you must delete a table and then add it to reposition it.

Use the MODIFY or CHANGE clause in the ALTER statement to change the column definition or name. T hese clauses have a similar effect, but use significantly different syntax.

Check out the CHANGE example given below -

mysql> ALTER TABLE products_tbl CHANGE discontinued status CHAR(4);

In a statement that uses CHANGE, specify the original column, and then specify the new column that will replace it. C heck out the MODIFY example below -

mysql> ALTER TABLE products_tbl MODIFY discontinued CHAR(4);

The ALTER command also allows you to change the default value. S ee example -

mysql> ALTER TABLE products_tbl ALTER discontinued SET DEFAULT N;

You can also use it to remove the default constraint by pairing it with the DROP clause -

mysql> ALTER TABLE products_tbl ALTER discontinued DROP DEFAULT;

Use ALTER to modify the table

Use the TYPE clause to change the table type -

mysql> ALTER TABLE products_tbl TYPE = INNODB;

Rename a table with THEME keyword -

mysql> ALTER TABLE products_tbl RENAME TO products2016_tbl;