Over time, system requirements change. These may necessitate the creation of new databases, tables, and columns as well as the altering of existing table structures. Changing a column's data type may be a trivial operation or a difficult one, depending on the source and target data types, as well as the data contained within the column. This blog will address some of the common challenges in changing a column's data type, along with strategies which you can employ to facilitate the process.
Alter Table Statement
The structure (schema) of existing tables can be altered using the ALTER TABLE statement. It's a Data Definition Language (DDL) statement, just like CREATE TABLE, DROP FUNCTION, and GRANT. It's basic syntax is:
ALTER TABLE table_to_change
what_to_change
(additional_arguments)
The ALTER TABLE statement may be utilized to change all sorts of table properties, from changing the table name to adding, dropping, and modifying columns.
One Statement, Varying Syntax
You may have noticed that, after the first line, the ALTER TABLE statement's syntax becomes quite vague. That's because it varies from vendor to vendor. For example:
In SQL Server
ALTER TABLE table_name ALTER COLUMN column_name column_type;
In PostgreSQL
ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition;
In Oracle, MySQL, and MariaDB
ALTER TABLE table_name MODIFY column_name column_type;
A Simple Example
Some databases, such as Oracle, don't allow you to run an ALTER query on tables that contain data. If you do, you'll get an error such as this:
Error report: SQL Error: ORA-01439: column to be modified must be empty to change datatype 01439. 00000 – “column to be modified must be empty to change datatype”
However, most database types do allow you to make changes to populated tables.
Here's a MySQL table in Navicat Premium's Table Designer that shows the column definitions:
We can execute an ALTER TABLE statement to increases the name (VARCHAR) column's capacity to 255 characters:
Converting a Column from VARCHAR to INT
It's not uncommon to see VARCHAR columns that contain numeric data. In some cases, it may be advantageous to change its type to a numeric type. In Navicat, we can set a column's type by choosing it from a drop-down:
Changes are made once the Save button is clicked. If you forget, Navicat will prompt you to save your changes when you close the Table Designer.
Data Truncated Error
You should avoid diminishing the size of a column's data type whenever possible; otherwise, you'll get a Data Truncated error, such as:
#1265 - Data truncated for column 'name' at row 2
There are no hard and fast rules for dealing with this error, but generally, you can update the value(s) in question yourself and then re-run the ALTER TABLE statement. For instance, here's a statement that truncates all name values to ten characters:
Conclusion
This blog outlined some of the common challenges in changing a column's data type, along with strategies which you can employ to facilitate the process.
Interested in Navicat Premium? You can try it for free for 14 days!
Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.
