How to 'move' a primary key via PL/SQL

I'm writing a PL-SQL script for my customer to modify the dataType of all columns with dataType CHAR.

So far so good. I've been able to write this.
1) I create a temp column
2) copy the values
3) drop the original column
4) make a new column (type and name)
5) copy all the values back
6) delete my temp column

the problems occur at point 3 ...
What if this column has a primary key constraint that has foreign keys pointing at them...

I can't drop neither the column nor the constraints due to the foreign keys...
I've tried if I could set the foreign keys disabled and I can but it doesn't seem to fix my problem.

Below, I've put my "disable foreign keys" script.

DECLARE
         varOwner VARCHAR(4) := 'FLX0';
      CURSOR foreignKeys IS
      SELECT table_name, constraint_name, r_constraint_name FROM ALL_CONSTRAINTS
                     WHERE OWNER = varOwner AND table_name LIKE 'TB_FLX%' AND constraint_type = 'R';
                    
BEGIN
  FOR position IN foreignKeys
  LOOP
            EXECUTE IMMEDIATE 'ALTER TABLE ' || position.table_name || ' DISABLE CONSTRAINT ' || position.constraint_name || '';
  END LOOP;
END;

Can anyone please help. I've been staring at this code and have been trying different approaches all afternoon... Or is the only way to do this a manual one ? I'm surprised I couldn't find more info on the web about this topic
The_PoetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
You should try this way.

1) I create a temp column
2) copy the values
3) update the original column to null
4) alter type of the original column
5) copy all the values back
6) delete my temp column
0
The_PoetAuthor Commented:
what statement should I execute to do point 3 and 4 and are these possible for oracle 8,9 and 10 ?
0
SujithData ArchitectCommented:
Yes these commands work in any version.

1) I create a temp column
alter table <table name> add (<temp col> <data type>);
2) copy the values
update <table name>
set <temp col> = <orig col>;
3) update the original column to null
update <table name>
set <orig col> = null;
4) alter type of the original column
alter table <table name>
modify (<orig col> <new data type>);
5) copy all the values back
update <table name>
set <orig col> = <temp col>;

After this step, query the table to see whether everything is ok and proceed to the next step.

6) delete my temp column
alter table <table name>
drop column <temp col>;
0
johnsoneSenior Oracle DBACommented:
If the original column is a primary key, then how are you going to update it to NULL?  Primary keys are not null fields.

Disabling the foreign keys does not work.  The reason is that even though they are disabled, they show a reference to the primary key field.  You need to actually drop the foreign and primary keys, then rebuild them after the columns have been switched out.

An alternate to this is to create a temporary table that would convert all the columns at once.  Then you can drop the original table and rename the temporary table to the original name.  Of course, this presents new problems of having to recreate indexes that are not on changed columns and recreating triggers and permissions.  It is an alternative though.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.