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
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
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>;
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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