2008-09-14

Disabling FK constraints

Problem: You want to insert some data in a bunch via insert or import that you know that is Foreign Key (FK) coherent but do not want to upload it in the right order.

solution:
1- make sure no one will the able to connect to the database besides you (or at least modify any data)
2- disable all the FK constraints or only the needed.

For all run the output of the following script:

SELECT 'alter table '||table_name||' disable constraint '||constraint_name||' ;' FROM user_constraints where constraint_type='R'

You can also add to the script only the tables you want to upload data to adding something like:
and table_name like 'TBL%'
or
and table_name in ('TABLE1','TABLE2')

3- upload the data
4- DO NOT FORGET: enabled the constraints you have previous disabled!!!

No comments:

Post a Comment

Os comentários são moderados.
The comments are moderated.