How to Add Column in MariaDB [5 Examples]

In this MariaDB tutorial, We will discuss how to add column in MariaDB. Let us explore the MariaDB Add column with a few examples and cover the following topics.

• MariaDB add column to table
• MariaDB add column if not exists
• MariaDB add column with default value
• MariaDB add column foreign key
• MariaDB add column to primary key
• MariaDB add column position

MariaDB add column to table

Once you create a table in MariaDB, we can add columns to the MariaDB table.

We can add columns to a table in MariaDB using the ALTER TABLE statement. We use the columns to store information in a MariaDB table.

The syntax is given below to add a new column to a MariaDB table.

ALTER TABLE old_table_name
  ADD new_column_name column_dataytpe;
  FIRST | AFTER column_name;

Where,

• old_table_name: It is the table where the column is added.
• new_column_name: It is the column that we want to add to the table.
• column_datatype: It is the column definition or data type like integer, float, etc.
• FIRST | AFTER column_name: It is optional and it instructs the MariaDB, Where in the table to create the column. if not specified then a new column is added at the end of the table.

Let us first create a table as demo_table by using the below script.

CREATE TABLE demo_table(id int);

This will create a new table as a demo_table with column id of data type integer.

Now, let us see how to add a column as demo_name of varchar data type by using the below query.

ALTER TABLE demo_table
ADD demo_name varchar(20);

To check the newly added column in the MariaDB table, run the below query.

SHOW COLUMNS FROM demo_table;

Also, read: How to install MariaDB

Add column if not exists in MariaDB

ALTER TABLE statement can also check for table existence in the MariaDB before adding the new column to the table. It has clauses like IF NOT EXISTS that check the existence of a column for adding the new column in the table.

Let’s add a new column to the table name demo_table.

ALTER TABLE demo_table 
ADD COLUMN IF NOT EXISTS demo_product varchar(50);

The above code will check for the existence of the table if it exists then the table will not be created otherwise it creates a new table.

This is how to add a column if not exists in the MariaDB table.

Read: How To Check MariaDB Version

How to add column with default value in MariaDB

In the MariaDB, We can add the new column to an existing table with a default value, for that DEFAULT keyword is used with ALTER TABLE statement.

Let’s again add the new column as demo_prod_price with the default value of zero.

ALTER TABLE demo_table
ADD COLUMN demo_prod_price float DEFAULT 0;

Now, insert the following records.

INSERT INTO demo_table(id,demo_name)VALUES(1,'product_1');

Here in the above code, we are inserting the data into the column id, demo_name, and not inserting any value to other columns.

As from the above output, the default value is inserted automatically in the column demo_prod_price that is 0. This is because while adding a column to demo_table we have defined the default value of the column to zero, if any value is not supplied, then this column will insert the zero in that column.

This is how to add a column with a default value in the MariaDB table.

Read: How to create a user in MariaDB

How to add column as primary key in MariaDB

Let us see, how to add a column as primary key in a MariaDB table. To make a column as the primary key, the keyword PRIMARY KEY is used in MariaDB.

In the table demo_table, we are going to add a new column as a primary key using the below code.

ALTER TABLE demo_table
ADD COLUMN demo_prod_id int PRIMARY KEY;

Here, we are adding a new column in the demo_table using ALTER TABLE statement. The column is defined as the primary key using the keyword PRIMARY KEY.

The output shows that the column demo_prod_id is the primary key, look at the key column of the table.

Read: How to Create Database in MariaDB

Add column as foreign key in MariaDB

Above we have created a column with a primary key, here we are going to create a column as a foreign key in a MariaDB table.

The primary key makes sure that the column should have unique values or not null values, whereas a foreign key is a set or group of columns that provide the connection between data in two tables.

The foreign key is a column that refers to a column (this kind of column is the primary key column) in another table.

Let’s create a new table as prod_type.

CREATE TABLE prod_type(demo_prod_type int,prod_name varchar(10));

Now, alter this table for adding the foreign key column using the below code.

ALTER TABLE prod_type
ADD COLUMN demo_prod_id int;

ALTER TABLE prod_type
ADD CONSTRAINT FOREIGN KEY (demo_prod_id) REFERENCES demo_table(demo_prod_id);

The shows that the demo_prod_id column is a foreign key column because the key column of the above output shows that the key column of the column demo_prod_id is MUL.

Read: How to Grant User Access to a MariaDB Database

MariaDB add column position

In the MariaDB, the column can be positioned at the front, end, and after specific columns using the ALTER TABLE statement. This can be achieved with the clauses FIRST or AFTER.

The full syntax is given below.

ALTER TABLE demo_table 
ADD COLUMN demo_prod_qty int
AFTER id;

Let’s add the new column to the existing table demo_table using the below code.

The above output shows that the column is added after the column id as we have specified in the query.

This is how to add a column to a specific position in the MariaDB table.

You may also like to read the following tutorials on MariaDB.

• How to Create Table in MariaDB
• MariaDB Vs SQL Server
• MariaDB Insert Multiple Rows
• How to Drop Column from MariaDB Table
• How to Create Trigger in MariaDB
• Replace Function in MariaDB

So in this tutorial, we have learned about “MariaDB add column” and covered the following topics.

• MariaDB add column to table
• MariaDB add column if not exists
• MariaDB add column with default value
• MariaDB add column foreign key
• MariaDB add column to primary key
• MariaDB add column position