miercuri, 8 ianuarie 2014

How to drop and recreate tables when the dependencies and foreign keys are way too complicate

If you noticed the prior post, i will tell you what context i used it in.
When you build a schema, soon you'll be found in the situation when it's too complicate to drop and recreate tables and other objects, because of the dependencies. Unnumbered ORA errors will appear as you step into the problem.
Let's get into the scenario:

- first of all, you need to drop the table, in order to modify it. This is imposible, because, some of his columns are foreign keys for other tables.

drop table app_forms;

- so, you need to disable the constraints. Unluckily, the prior post contains a script that is not aware of the precedence of all the constraints. Here will apply the CASCADE reserved word, the one from the 8th line. This is the reason is there.  

declare
nume_ct varchar2(50);
begin 
for nume_ct in (select constraint_name,u.table_name from user_constraints c, user_tables u where c.table_name = u.table_name)
    loop 
    EXECUTE IMMEDIATE 'alter table ' || nume_ct.table_name || ' disable constraint ' ||nume_ct.constraint_name;
    end loop;                
end;


- next, try to drop the table. Sometimes, i don't know why, the following error will appear: ORA-02449: unique/primary keys in table referenced by foreign keys. Once again, i don't know why, because all the constraints are disabled. Go further, by adding CASCADE CONTRAINTS in you drop command.

drop table app_forms cascade constraints;

- next step, recreate the table with the modified script. Error: ORA-02270: no matching unique or primary key for this column-list. That's because the reinforced foreign key constraints don't find anymore the primary keys referenced. So, run the enabling constraints script, from the prior post, and after that, create the table. We'll work!

Niciun comentariu:

Trimiteți un comentariu