How to Change Column Datatype or Property in MySQL

1. Change Column Datatype or Property in MySQL
2. Use the ALTER TABLE CHANGE Keyword to Modify the Datatype, Constraints, or Property of a Single Column in a MySQL Database Table
3. Use the ALTER TABLE MODIFY Keyword to Modify the Datatype, Constraints, or Property of a Single Column in a MySQL Database Table
4. Use the ALTER TABLE Keyword to Modify the Datatype, Constraints, or Property of Multiple Columns in a MySQL Database Table

Database design is often iterative, with requirements changing and initial design re-adjusted. MySQL is a robust RDBMS that allows the modification of existing/declared table columns with the ALTER TABLE keyword.

This tutorial illustrates using the ALTER TABLE keyword for modifying the datatype, constraints, or properties of existing columns in a MySQL database.

Change Column Datatype or Property in MySQL

The ALTER TABLE keyword can combine with other keywords for achieving the necessary modification. In MySQL, the CHANGE keyword is the main extension to the standard SQL.

However, the MODIFY keyword is an available extension for the sake of compatibility with Oracle.

The CHANGE or MODIFY keywords achieve the same result, with slight trade-offs in terms of convenience of syntax and robustness. To illustrate these concepts, let us create a programming_languages database with a table named Details.

/* Here goes the definition of the database */
CREATE DATABASE programming_languages;
USE programming_languages;

-- Creating a details table
CREATE TABLE Details(
	id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(25) UNIQUE,
    year_released VARCHAR (5),
    PRIMARY KEY(id)
    );
-- Populating Details with information
INSERT INTO Details (name, year_released) VALUES ('python', 1991),('c++', 1985),('Java', 1995);
SELECT * FROM Details ORDER BY id;

DESCRIBE Details year_released;  -- Checking the column information

Output:

id	name	year_released
1	python	1991
2	c++		1985
3	Java	1995
-----------------------------------------------------------------------------------------
Field			Type		Null	Key	Default	Extra
year_released	varchar(5)	YES			NULL

Let us modify the year_released column to implement a YEAR data type instead of a VARCHAR.

Use the ALTER TABLE CHANGE Keyword to Modify the Datatype, Constraints, or Property of a Single Column in a MySQL Database Table

The CHANGE keyword can implement three types of modifications on a column at a time.

1. It can redefine the datatype and constraints of a column.
2. It can rename a column.
3. It can re-order the columns in a table with the FIRST or AFTER keywords.

 -- changing the datatype of year_released from VARCHAR to YEAR
 ALTER TABLE Details
 CHANGE year_released year_released YEAR;

 DESCRIBE Details year_released;  -- Running again, to confirm changes

Output:

Field			Type	Null	Key	Default	Extra
year_released	year	YES			NULL

Notice how the name of the target column was included twice. This is the syntax inconvenience of using CHANGE, as it expects the name of the modified column to be specified.

We re-specify the name as year_released to keep the original column name. However, this supposed inconvenience becomes efficient when there is a requirement to modify both the property and the name of a column.

Then, such an operation can be executed in the same line.

For example, let us change the column name to year_of_release and include a NOT NULL and DEFAULT constraint.

-- Altering YET AGAIN
ALTER TABLE Details
CHANGE year_released year_of_release YEAR NOT NULL DEFAULT '1990';

DESCRIBE Details year_of_release;

Output:

Field			Type	Null	Key	Default	Extra
year_of_release	year	NO			1990

While this works, the recommended approach for changing a column name is with the RENAME COLUMN keyword. RENAME only requires the current/old name of the column and the new name.

ALTER TABLE Details
RENAME COLUMN year_of_release TO year_released;

DESCRIBE Details year_released;  -- Viewing changes

Output:

Field			Type	Null	Key	Default	Extra
year_released	year	NO			1990

For extra details on the usage of the CHANGE keyword, have a look at this official documentation.

Use the ALTER TABLE MODIFY Keyword to Modify the Datatype, Constraints, or Property of a Single Column in a MySQL Database Table

The alternative MODIFY keyword offers the same functionality as the CHANGE keyword, but it does not support the rename of a column in its expression. To rename a column, combine the MODIFY keyword with the RENAME COLUMN keyword as previously described.

This time, let us redefine the name column to accept a VARCHAR of thirty (30) characters with a NOT NULL constraint.

-- Altering with MODIFY
ALTER TABLE Details
MODIFY name VARCHAR(30) NOT NULL DEFAULT 'None';

DESCRIBE Details name;

Output:

Field	Type		Null	Key	Default	Extra
name	varchar(30)	NO		UNI	None

Notice how the UNIQUE constraint carries over to the altered column. Constraints like PRIMARY KEY or UNIQUE do not need a re-specification.

Now, let us rename the column to prog_language_name and allow NULL values by combining the MODIFY keyword with RENAME COLUMN.

ALTER TABLE Details
-- Allowing Null values by not specifying NOT NULL
MODIFY name VARCHAR(30) DEFAULT 'None';

-- Renaming the column
ALTER TABLE Details
RENAME COLUMN name TO prog_language_name;

-- Viewing the changes
DESCRIBE Details prog_language_name;

Output:

Field				Type		Null	Key	Default	Extra
prog_language_name	varchar(30)	YES		UNI	None

Use the ALTER TABLE Keyword to Modify the Datatype, Constraints, or Property of Multiple Columns in a MySQL Database Table

The methods previously illustrated are similarly applicable to multiple columns. However, the MODIFY or CHANGE statements are repeated based on the number of columns to be changed.

Further details on this are available via this reference.

Let us set a NOT NULL constraint and a default value for the year_of_release and prog_language_name columns.

ALTER TABLE Details
MODIFY prog_language_name VARCHAR(30) NOT NULL DEFAULT 'No Data',
MODIFY year_released year NOT NULL DEFAULT '1950';

DESCRIBE Details

Output:

Field				Type		Null	Key		Default		Extra
id					int			NO		PRI		NULL		auto_increment
prog_language_name	varchar(30)	NO		UNI		No Data
year_released		year		NO				1950