How to Change Column in MariaDB

In this MariaDB tutorial, we will learn about the “MariaDB change column” and cover the following topics.

• How to change column data type in MariaDB
• MariaDB change column name
• How to change column order in MariaDB
• MariaDB change column size
• How to change column to not null in MariaDB
• How to change column to primary key in MariaDB
• MariaDB change column collation
• How to change column default value in MariaDB

MariaDB change column data type

First let’s know about ‘What is data type?’, The data type is an attribute that defines the type of data that a variable or column can hold like integer, float, character, etc. So whenever the columns are created in the MariaDB then its data type is defined that the type of data it can hold.

Here in this section, we will change the data type of the already created column in the table using the ALTER TABLE statement with clause or parameter MODIFY that modify or change the column data type.

Create the new table as change_dtype using the below query.

CREATE TABLE change_dtype(id int DEFAULT 0,col_name varchar(20));

Look at the above output, the column col_name has a data type of varchar means it stores the string value, so here we will change the data type of this column.

ALTER TABLE change_dtype MODIFY col_name char(5);

The above code modifies the column col_name data type from varchar to char using the clause MODIFY with the statement ALTER TABLE.

Now look again at the output of the code, we have changed the data type of the column col_name.

This is how to change column data type in MariaDB.

Also, check: MariaDB Update Statement

Change column name in MariaDB

The ALTER TABLE statement is used to change the column name in MariaDB, the RENAME COLUMN clause is used with ALTER TABLE statement to rename the column name.

The syntax is given below.

ALTER TABLE tbl_name
RENAME COLUMN old_col_name to new_col_name;

Let’s change the column name of the col_name column in the change_dtype table that we have created in the above section.

ALTER TABLE change_dtype
RENAME COLUMN col_name TO dtype_col_name;

Here in the above code, we are changing the column name from col_name to dtype_col_name of the table change_type using the ALTER TABLE statement.

Actually, To rename or change the column name we are using the clause RENAME COLUMN with ALTER TABLE statement.

The output shows the changed column name from col_name to dtype_col_name.

This is how to change column name in MariaDB.

Read: How to Remove User in MariaDB

How to Change column order in MariaDB

The ALTER TABLE statement with clause MODIFY and FIRST | AFTER is used to change the column position or order in the MariaDB.

The syntax is given below.

ALTER TABLE tbl_name
MODIFY col_name FIRST | AFTER;

Where the FIRST means put the column in the front of the table, and AFTER means put the column after a specific column.

Now we will create the table with the column, the column name of the table will be based on country names like United States, Canada, Australia, etc.

CREATE TABLE country_tbl(id int, United_States varchar(20), Canada varchar(20), Austrailia varchar(10));

Look at the output of the table country_tbl, note the order of the column of the table.

Now change the column order of United_States after Canada using the below code.

ALTER TABLE country_tbl
MODIFY United_States varchar(20) AFTER Canada;

The output shows that we have changed the order of the column United_States.

This is how to change column order in MariaDB.

Read: How to Grant All Privileges in MariaDB

Change column size in MariaDB

To change the column size, we need to provide the new size of the column data type using the ALTER TABLE statement with MODIFY clause in MariaDB.

Let’s change the United_States column size from 20 to 50 in the table country_tbl.

ALTER TABLE country_tbl
MODIFY United_States varchar(50);

This is how to change column size in MariaDB.

Read: How to Create View in MariaDB

MariaDB change column to not null

The NOT NULL is a constraint that ensures whatever the value stored in the column is not null in MariaDB. The existing column can be changed to the not null column using the ALTER TABLE statement with MODIFY clause.

There is two-step to change the column to not null.

First: change or update the null values to non-null and Second: change the column to include the not-null constraint.

Let’s change the column col_name to not null column of the table change_dtype.

ALTER TABLE change_dtype
MODIFY col_name varchar(20) NOT NULL;

In the above code, we are changing the column col_name with a new constraint NOT NULL to ensure that the column should not store the null values.

This is how to change column to not null in MariaDB

Read: Replace Function in MariaDB

MariaDB change column to primary key

In MariaDB, the column type can be changed to a primary key using the ALTER TABLE statement with clause MODIFY. The column with the primary key contains the unique records and can’t have the null value.

Look at the above table, here the id field has not any key in the key column as we can see.

Let’s change the id column of the table country_tbl to the primary key.

ALTER TABLE country_tbl
MODIFY id int PRIMARY KEY;

Now, the id column or field has constraints like PRI which is for the primary key.

This is how to change column to a primary key in MariaDB.

Read: How to import CSV files in MariaDB

MariaDB change column collation

The collation in the MariaDB is the set of character and character encoding rules that define how information is stored in the column.

To check or see the collation of the column in the table use the below code.

SHOW FULL COLUMNS FROM table_name;

Now, check the collation of the columns in the table change_dtype.

SHOW FULL COLUMNS FROM change_dtype;

Look in the above table at column col_name, where the collation column shows the latin1_swedish_ci character set.

Let’s change this collation to utf8mb3_spanish_ci using the below code.

ALTER TABLE change_dtype MODIFY COLUMN col_name varchar(20) COLLATE utf8mb3_spanish_ci;

Now, we have changed the column collation to utf8mb3_spanish_ci.

This is how to change column collation in MariaDB.

Read: MariaDB Vs SQLite

How to change column default value in MariaDB

In MariaDB, The DEFAULT value is a preset value that is assigned to column type when no value is assigned explicitly to that column or while inserting some data into the table.

As we can see in the below table column id has a default value of 0 which means when we don’t provide any value to that column while inserting the values in the column. Then 0 is assigned to that column.

Let’s change this default value to 1 using the below query.

ALTER TABLE change_dtype
MODIFY id int DEFAULT 1;

As we can see in the above output, the id column has the default value of 1.

This is how to change column default value in MariaDB.

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

• How to Drop Column from MariaDB Table
• MariaDB Timestamp
• MariaDB ERROR 1064
• MariaDB Select Into + Examples
• MariaDB Comment – How to use
• How to Create Function in MariaDB
• How to Create Trigger in MariaDB

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

• Change column data type in MariaDB
• How to change column name in MariaDB
• MariaDB change column order
• How to change column size in MariaDB
• MariaDB change column to not null
• How to change column to primary key in MariaDB
• MariaDB change column collation
• How to change column default value in MariaDB