Re-Create Reference Constraints Dynamically

I currently have a project to modify several tables from non-partitioned to partition. A fairly easy, although tedious process that I documented in this blog. However, one of the stumbling points was re-creating dropped referential constraints which is required when using dbms_redefinition since the tables with references to the table being modified will point to the original non-partitioned table with the interim table name. We can’t drop the interim table (which is now the original table) so that we can rename the constraints and indexes appropriately (back to their original names). The problem wasn’t recreating these constraints but finding the most efficient method of per-creating the script. I always perform all my maintenance tasks from scripts that have been tested through all the environments. Of course, the constraints would be lost if they are dropped prior to creating a script to create them. I found a few of different approaches. I didn’t need anything specific or too complex so I turned to the internet which is almost always loaded with scripts ready to be used. I settled on the following approach by Duncan Berriman.

I did modify the script slightly to include the owner of the table. This allows me to use the script as myself for multiple schemas.

Happy redefinition!



Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: