Bootstrap FreeKB - mySQL / MariaDB - Add a new column to a table
mySQL / MariaDB - Add a new column to a table

Updated:   |  mySQL / MariaDB articles

The alter table command can be used to add a new column to a table. You will first need to log into your MariaDB or mySQL server or configure passwordless authentication and then use the -e command line option and the user must have the Alter_priv.

You will probably want to show the columns in the table that you want to add a column to.The show columns or describe <tablename> commands can be used to list the columns in the table. In this example, the columns in table001 in the database named db001 will be described.

use db001; describe table001;


Or like this.

describe db001.table001;


Something like this should be returned.

| Field           | Type          | Null | Key | Default | Extra          |
| id              | int(11)       | NO   | PRI | NULL    | auto_increment |
| column001       | varchar(200)  | NO   |     | NULL    |                |
| column002       | varchar(1000) | NO   |     | NULL    |                |
| column003       | varchar(2000) | NO   |     | NULL    |                |


Add a new column.


Notice in this example that the "id" column has the PRI (PRIMARY KEY) set and auto_increment. Each table can have only one column set as the PRIMARY KEY. For this reason, the new column cannot have the PRIMARY KEY set (nor would it make any sense for the new column to have the PRIMARY KEY set). Refer to Understanding PRIMARY KEY and auto_increment.

alter table db001.table001 add column004 varchar(100) not null;


Did you find this article helpful?

If so, consider buying me a coffee over at Buy Me A Coffee


Add a Comment

Please enter 9e0226 in the box below so that we can be sure you are a human.