MariaDB Alter Table

Summary: in this tutorial, you will learn how to use the MariaDB alter table statement to add a column, alter a column, rename a column, drop a column and rename a table.

Setting up a sample table

The following statement creates a new table called customers for demonstration:

create table customers(
    id int auto_increment,
    name varchar(255) not null,
    created_at timestamp default current_timestamp,
    is_active bool not null default false,
    primary key(id)
);
Code language: SQL (Structured Query Language) (sql)

Using MariaDB alter table to add a column or columns to a table

The alter table add statement allows you to add one or more columns to a table.

1) Add a column to a table

To add a column to a table, you use the alter table add syntax:

alter table table_name
add 
    new_column_name column_definition
    Code language: SQL (Structured Query Language) (sql)

[first | after column_name]

In this syntax:

• First, specify the name of the table that you want to add a column after the alter table keywords.
• Second, specify the name of the new column after the add keyword.
• Third, specify the datatype, maximum size, and column constraint of the new column.
• Finally, specify the position of the new column in the table. It is possible to add a column after an existing column (after column_name) or as the first column (first). If you skip this option, MariaDB will append the new column at the end of the column list.

This example uses the alter table add statement to add a column at the end of the customers table:

alter table customers
add email varchar(255) not null;
Code language: SQL (Structured Query Language) (sql)

To show the column list of the customers table, you use the describe statement:

describe customers;
Code language: SQL (Structured Query Language) (sql)

2) Add multiple columns to a table

The following syntax allows you to add multiple columns to a table:

alter table table_name
    add new_column_name column_definition
    [first | after column_name],
    add new_column_name column_definition
    [first | after column_name],
    ...;
Code language: SQL (Structured Query Language) (sql)

For example, this statement adds two columns phone and address to the customers table:

alter table customers
add phone varchar(15),
add address varchar(255);
Code language: SQL (Structured Query Language) (sql)

Here is the new structure of the customers table:

describe customers;
Code language: SQL (Structured Query Language) (sql)

Using MariaDB alter table to modify columns

1) Modify a column

To change the characteristics of a column, you use the alter table modify statement:

alter table table_name
modify column_name column_definition
[ first | after column_name];    
Code language: SQL (Structured Query Language) (sql)

Suppose that you want to modify the phone column to a not null column whose type is varchar with the maximum size 20

First, display the columns of the customers table:

describe customers;
Code language: SQL (Structured Query Language) (sql)

Then, modify the phone column:

alter table customers 
modify phone varchar(20) not null;
Code language: SQL (Structured Query Language) (sql)

Finally, show the column list of the customers table again to verify the modification:

describe customers;
Code language: SQL (Structured Query Language) (sql)

2) Modify multiple columns

To modify multiple columns, you use the following syntax:

alter table table_name
    modify column_name column_definition
    [ first | after column_name],
    modify column_name column_definition
    [ first | after column_name],
    ...;
Code language: SQL (Structured Query Language) (sql)

First, show the column list of the customers table:

describe customers;
Code language: SQL (Structured Query Language) (sql)

Then, modify the email and address columns:

alter table customers 
modify email varchar(255),
modify address varchar(255) after name;
Code language: SQL (Structured Query Language) (sql)

Finally, display the new column list of the customers table to verify the changes:

describe customers;
Code language: SQL (Structured Query Language) (sql)

Using MariaDB alter table to rename a column in a table

To rename a column, you use the following syntax:

alter table table_name
change column original_name new_name column_definition
[first | after column_name];
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table from which you want to rename the column after the alter table keywords.
• Second, specify the name of the column and the new name followed by the column definition after the change column keywords.
• Third, specify the new position of the column by using the first or after column_name clause.

This example uses alter table change column statement to rename the column address to office_address:

alter table customers
change column address office_address varchar(255) not null;
Code language: SQL (Structured Query Language) (sql)

The following statement shows the new column list of the customers table:

describe customers;
Code language: SQL (Structured Query Language) (sql)

Using MariaDB alter table to drop a column

To drop a column in a table, you use the alter table drop column statement:

alter table table_name
drop column column_name;    
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table that you want to drop a column or columns after the alter table keywords.
• Second, specify the name of the column that you want to drop after the drop column keywords.

The following example removes the office_address column from the customers table:

alter table customers
drop column office_address;
Code language: SQL (Structured Query Language) (sql)

Using MariaDB alter table statement to rename a table

To rename a table, you use the alter table rename to statement:

alter table table_name
rename to new_table_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table that you want to rename after the alter table keywords.
• Second, specify the new table name after the rename to keywords.

The following statement renames the table customers to clients:

alter table customers 
rename to clients; 
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you learned how to use the MariaDB alter table statement to add a column, modify a column, rename a column, drop a column, and rename a table.