SQL Alter Table MODIFY

Changing Table Definitions with alter and modify

Most RDBMS systems allow you to change the structure of a table with the
ALTER TABLE command.

Syntax for ALTER with MODIFY can varies from RDBMS to RDBMS,
so check your system manuals for specifics.

To modify the type of data in a column can sometimes have unexpected resultet. It is therefore important that you are sure of what you are doing.

Yet here is the syntax and examples of how this is done:

Change the type for a Column:

To change the column type in an empty table is normally no problem,
but if the table contains data the result will differ for each RDBMS.

MySQL, Oracle:

ALTER TABLE table_name MODIFY column_name column_type;

Example:

ALTER TABLE CUSTOMER MODIFY TELEPHONE_NUMBER INT;

In Oracle: If the column in the table contains characters that can not be convert to new type (here INT) there will be NO conversion. Error message will be given.

In MySQL: If the column in the table contains characters that can not be convert to new type (here INT)
there will be a conversion and the column data will be a default value (0). Error message will be given.

SQL server:

ALTER TABLE table_name ALTER COLUMN column_name column_type;

Example:

ALTER TABLE CUSTOMER ALTER COLUMN TELEPHONE_NUMBER INT;

In SQL server: If the column in the table contains characters that can not be convert to new type (here INT) there will be NO conversion. Error message will be given.