How to Change Column Type in MariaDB

How do I change a column’s data type in MariaDB without recreating the table?

Use ALTER TABLE … MODIFY or CHANGE to safely alter a column’s data type without recreating the table.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why would you change a column type in MariaDB?

Adjusting a column’s data type lets you store larger values, enforce stricter constraints, or improve storage efficiency. Typical cases include converting VARCHAR order totals to DECIMAL or switching INT flags to BOOLEAN for clarity.

What is the safest way to change a column type?

Always back up the table, disable foreign-key checks if needed, and run the ALTER TABLE statement inside a transaction when using InnoDB. For mission-critical tables, test on a clone first.

How do I use ALTER TABLE … MODIFY?

MODIFY keeps the column name intact and only changes its definition. Ideal when the name stays the same—e.g., increasing VARCHAR length or switching to DECIMAL.

Example

ALTER TABLE Orders MODIFY total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00;

How do I use ALTER TABLE … CHANGE?

CHANGE requires you to repeat (or rename) the column name and lets you alter its data type and position simultaneously. Use it when renaming columns or moving them with FIRST/AFTER.

Example

ALTER TABLE Customers CHANGE created_at registered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER email;

How to update dependent indexes and constraints?

MariaDB automatically rebuilds affected indexes, but you must recreate CHECK constraints referencing the altered column. Verify foreign-key columns share the same type and length.

What precautions should I take on large tables?

ALTER TABLE locks the table for the operation’s duration unless online DDL is enabled. Schedule during low-traffic windows or use pt-online-schema-change for zero-downtime migrations.

Example: migrate VARCHAR to INT in Orders table

Suppose customer_id is VARCHAR(10) but should be INT. First, validate data, then run:

ALTER TABLE Orders MODIFY customer_id INT UNSIGNED NOT NULL;

What are best practices?

Keep column and referenced key types identical, set sensible DEFAULT values, monitor disk space, and update ORM schemas or application code immediately after deployment.

Common mistakes and how to avoid them

Omitting NULL/NOT NULL: MariaDB resets the NULLability if you do not specify it. Always repeat the desired constraint.
Forgetting to copy DEFAULT: CHANGE without DEFAULT drops the old default. Explicitly restate default values.

Why How to Change Column Type in MariaDB is important

How to Change Column Type in MariaDB Example Usage


-- Convert nullable VARCHAR customer_id to NOT NULL INT
ALTER TABLE Orders
  MODIFY customer_id INT UNSIGNED NOT NULL;

How to Change Column Type in MariaDB Syntax


-- Basic pattern using MODIFY
ALTER TABLE table_name
  MODIFY [COLUMN] column_name new_data_type [NULL | NOT NULL]
                                         [DEFAULT default_value]
                                         [AFTER other_column];

-- Pattern using CHANGE (allows rename & reposition)
ALTER TABLE table_name
  CHANGE [COLUMN] old_column_name new_column_name new_data_type
                                         [NULL | NOT NULL]
                                         [DEFAULT default_value]
                                         [FIRST | AFTER other_column];

-- Ecommerce example: widen product name
ALTER TABLE Products
  MODIFY name VARCHAR(300) NOT NULL;

-- Ecommerce example: rename & convert order total
ALTER TABLE Orders
  CHANGE total_amount order_total DECIMAL(12,2) NOT NULL DEFAULT 0.00;

Common Mistakes

Omitting NULL/NOT NULL: MariaDB may change nullability unexpectedly. Fix by explicitly adding the desired NULL or NOT NULL clause in the ALTER statement.
Ignoring DEFAULT values: MODIFY or CHANGE drops the old default unless restated. Always include DEFAULT when you need it preserved.