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.

PostgreSQL:
ALTER TABLE table_name ALTER COLUMN column_name 
   TYPE INTEGER  [ USING  column_name::column_type]; 
Example:
ALTER TABLE CUSTOMER ALTER COLUMN TELEPHONE_NUMBER 
   TYPE INTEGER USING TELEPHONE_NUMBER::INTEGER; 

In PostgreSQL: 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 some cases you don't need the USING clause.

Example - change the TELEPHONE_NUMBER back to en VARCHAR type:
ALTER TABLE CUSTOMER ALTER COLUMN TELEPHONE_NUMBER 
   TYPE VARCHAR(20); 
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.