CODEWITHSUNDEEP
oracle10g
MastanSky
MastanSky

Reputation: 57

Modifying column data type

I have two tables like Samp1(Parent Table) and Samp2 (Child table)

Parent Table :Samp1

column            Datatype     Constraint
----------------------------------------
Id Number(6)      Primary       Key

Child table :Samp2

column              Datatype      Constraint
----------------------------------------------
Id                  Number(6)      Foriegn Key

Then how to modify column data type Number to Varchar2(10) both parent and child table at a time, can I?

Upvotes: 0

Views: 69

Answers (1)

Justin Cave
Justin Cave

Reputation: 231861

You can't do this in one step. Assuming the tables have data, you'd probably need to do something like

  • Add a new column (e.g. id_varchar) to your parent table
  • Populate the data in this new column from the data in the existing column
  • Add that new column to the child table
  • Populate the data in this new column from the data in the existing column
  • Create a new foreign key constraint for the new column
  • Drop the existing foreign key constraint
  • Drop the existing primary key constraint
  • Drop the existing id columns from both tables
  • Rename the id_varchar column to id in each table
  • Create a new primary key constraint on the parent table

Normally, this would require some downtime since you generally don't want sessions modifying data while you're doing this. If you need to do this online, you could potentially use the dbms_redefinition package which would involve creating new copies of both tables.

Upvotes: 1

Related Questions