Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

everything is in the question.

I have a table named tableA on which we have indexes and foreign keys and constraints.

I have to modify content in a column called column1. This being a production system, I want to

1) create a copy of tableA called tableA_temp
2) Perform the data change (some concatenation and regexp_replace)
3) Obtain validation that the change is correct
4) rename tableA to tableA_old
4) Rename tableA_temp to tableA.

Once renamed to tableA, would the constraints and indexes apply to the new tableA?

My guess is yes but I would need a little validation.

share|improve this question
2  
Unless the column you're changing is vital to the indexes or something similar, I'd think about adding a new column, setting/updating that from the old one, and when you're happy swap them over with a couple of alter table ... rename column ... commands, and at some point drop the original (now renamed) column. Seems slightly less messy than doing it at table level. –  Alex Poole Sep 11 '12 at 17:57
add comment

2 Answers

up vote 1 down vote accepted

You can use the DBMS_REDEFINITION package to "re-organise" a table like this. It provides functionality for you to create a temporary table with modified contents of an existing table as you describe. It also allows you to copy over the grants, triggers, indexes etc to the new table, then switch the tables over so the temporary table becomes the live table.

See this article for a worked demo.

share|improve this answer
    
+1 Although this doesn't answer the question, it is pertinent/useful information addressing the underlying concern. –  Leigh Riffel Jan 29 '13 at 14:20
add comment

No.

Ok, it depends on if you create them on the copy.

share|improve this answer
    
OK. Thanks for the answer :) I'm adding constraint and index creation to the copy. –  ndefontenay Sep 11 '12 at 14:41
1  
+1 No is the correct answer. Even if the constraints are created on the copy, they apply to the copy and aren't applied from a dropped table. –  Leigh Riffel Jan 29 '13 at 14:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.