totn MariaDB

MariaDB: ALTER TABLE Statement

This MariaDB tutorial explains how to use the MariaDB ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with syntax and examples).

Description

The MariaDB ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The MariaDB ALTER TABLE statement is also used to rename a table.

Add column in table

Syntax

The syntax to add a column in a table in MariaDB (using the ALTER TABLE statement) is:

ALTER TABLE table_name
  ADD new_column_name column_definition
    [ FIRST | AFTER column_name ];
table_name
The name of the table to modify.
new_column_name
The name of the new column to add to the table.
column_definition
The datatype and definition of the column (NULL or NOT NULL, etc).
FIRST | AFTER column_name
Optional. It tells MariaDB where in the table to create the column. If this parameter is not specified, the new column will be added to the end of the table.

Example

Let's look at an example that shows how to add a column in a MariaDB table using the ALTER TABLE statement.

For example:

ALTER TABLE websites
  ADD host_name varchar(40)
    AFTER server_name;

This MariaDB ALTER TABLE example will add a column called host_name to the websites table. It will be created as a column that allows NULL values and will appear after the server_name field in the table.

Add multiple columns in table

Syntax

The syntax to add multiple columns in a table in MariaDB (using the ALTER TABLE statement) is:

ALTER TABLE table_name
  ADD new_column_name column_definition
    [ FIRST | AFTER column_name ],
  ADD new_column_name column_definition
    [ FIRST | AFTER column_name ],
  ...
;
table_name
The name of the table to modify.
new_column_name
The name of the new column to add to the table.
column_definition
The datatype and definition of the column (NULL or NOT NULL, etc).
FIRST | AFTER column_name
Optional. It tells MariaDB where in the table to create the column. If this parameter is not specified, the new column will be added to the end of the table.

Example

Let's look at an example that shows how to add multiple columns in a MariaDB table using the ALTER TABLE statement.

For example:

ALTER TABLE websites
  ADD host_name varchar(20)
    AFTER server_name,
  ADD creation_date date;

This ALTER TABLE example will add two columns to the websites table - host_name and creation_date.

The host_name field will be created as a varchar(20) column that allows NULL values and will appear after the server_name column in the table. The creation_date column will be created as a date column and will appear at the end of the table.

Modify column in table

Syntax

The syntax to modify a column in a table in MariaDB (using the ALTER TABLE statement) is:

ALTER TABLE table_name
  MODIFY column_name column_definition
    [ FIRST | AFTER column_name ];
table_name
The name of the table to modify.
column_name
The name of the column to modify in the table.
column_definition
The modified datatype and definition of the column (NULL or NOT NULL, etc).
FIRST | AFTER column_name
Optional. It tells MariaDB where in the table to position the column, if you wish to change its position.

Example

Let's look at an example that shows how to modify a column in a MariaDB table using the ALTER TABLE statement.

For example:

ALTER TABLE websites
  MODIFY host_name varchar(50);

This ALTER TABLE example will modify the column called host_name to be a data type of varchar(50) and allow NULL values.

Modify Multiple columns in table

Syntax

The syntax to modify multiple columns in a table in MariaDB (using the ALTER TABLE statement) is:

ALTER TABLE table_name
  MODIFY column_name column_definition
    [ FIRST | AFTER column_name ],
  MODIFY column_name column_definition
    [ FIRST | AFTER column_name ],
  ...
;
table_name
The name of the table to modify.
column_name
The name of the column to modify in the table.
column_definition
The modified datatype and definition of the column (NULL or NOT NULL, etc).
FIRST | AFTER column_name
Optional. It tells MariaDB where in the table to position the column, if you wish to change its position.

Example

Let's look at an example that shows how to modify multiple columns in a MariaDB table using the ALTER TABLE statement.

For example:

ALTER TABLE websites
  MODIFY host_name varchar(45)
    AFTER website_id,
  MODIFY server_name varchar(30) NOT NULL;

This ALTER TABLE example will modify two columns to the websites table - host_name and server_name.

The host_name field will be changed to a varchar(45) column that allows NULL values and will appear after the website_id column in the table. The server_name column will be modified to a varchar(30) NOT NULL column (and will not change position in the websites table definition, as there is no FIRST | AFTER specified).

Drop column in table

Syntax

The syntax to drop a column in a table in MariaDB (using the ALTER TABLE statement) is:

ALTER TABLE table_name
  DROP COLUMN column_name;
table_name
The name of the table to modify.
column_name
The name of the column to delete from the table.

Example

Let's look at an example that shows how to drop a column in a MariaDB table using the ALTER TABLE statement.

For example:

ALTER TABLE websites
  DROP COLUMN host_name;

This ALTER TABLE example will drop the column called host from the table called websites.

Rename column in table

Syntax

The syntax to rename a column in a table in MariaDB (using the ALTER TABLE statement) is:

ALTER TABLE table_name
  CHANGE COLUMN old_name new_name 
    column_definition
    [ FIRST | AFTER column_name ]
table_name
The name of the table to modify.
old_name
The column to rename.
new_name
The new name for the column.
column_definition
The datatype and definition of the column (NULL or NOT NULL, etc). You must specify the column definition when renaming the column, even if it does not change.
FIRST | AFTER column_name
Optional. It tells MariaDB where in the table to position the column, if you wish to change its position.

Example

Let's look at an example that shows how to rename a column in a MariaDB table using the ALTER TABLE statement.

For example:

ALTER TABLE websites
  CHANGE COLUMN host_name hname
    varchar(25);

This MariaDB ALTER TABLE example will rename the column called host_name to hname. The column will be defined as a varchar(25) data type that allows NULL values.

Rename table

Syntax

The syntax to rename a table in the MariaDB using the ALTER TABLE statement is:

ALTER TABLE table_name
  RENAME TO new_table_name;
table_name
The table to rename.
new_table_name
The new table name.

Example

Let's look at an example that shows how to rename a table in MariaDB using the ALTER TABLE statement.

For example:

ALTER TABLE websites
  RENAME TO sites;

This ALTER TABLE example will rename the websites table to sites.

Share on: