Link to home
Start Free TrialLog in
Avatar of The_Poet
The_Poet

asked on

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
Avatar of Sujith
Sujith
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of The_Poet
The_Poet

ASKER

what statement should I execute to do point 3 and 4 and are these possible for oracle 8,9 and 10 ?
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>;
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial