Saturday, September 4, 2010

How to change data-type of primary index column for a given Table

Situation: There is a requirement to change the datatype of primary index column .

Solution:
  1. You could run a Show table command to get the exact DDL, then change datatype ( say for example char(8)) to datatype varchar(10) .
  2. Run the ddl script to create the table.
  3. Then you can run an insert select command to insert data into the new table.
  4. Since the PI of both the tables are same, the operation would be pretty fast.
  5. Then DROP the original table and rename the new one to the old one.

Inserting data into an empty table is very quick because there is not reference to transient journal.

please note , if you use

  • CREATE new_table AS existing_table" preserves all column attributes and indexes (just Triggers and Foreign Keys are removed)
  • But using CREATE new_table AS (SELECT * FROM existing_table) will remove NOT NULL and TITLE properties.

No comments:

Post a Comment