Home » RDBMS Server » Server Administration » Rename table
Rename table [message #227547] Wed, 28 March 2007 10:40 Go to next message
marks20101
Messages: 74
Registered: May 2005
Member
I want to rename a table I know the command, but what should I do about the primary key and other indexes on the table. Should I drop and recreate the PK and indexes or can I modify them? What would be a good work flow?

Thanks,
Mark S
Re: Rename table [message #227557 is a reply to message #227547] Wed, 28 March 2007 11:10 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

HI,


SQL> select constraint_name,status from user_constraints where table_name = 'T';


CONSTRAINT_NAME                STATUS
------------------------------ --------
NO_PK                          ENABLED

SQL> select index_name,status from user_indexes where table_name = 'T';

INDEX_NAME                     STATUS
------------------------------ --------
T_IND                          VALID

SQL> rename t to newname_tt;

Table renamed.

SQL> select constraint_name,status from user_constraints where table_name = 'NEW
NAME_TT';

CONSTRAINT_NAME                STATUS
------------------------------ --------
NO_PK                          ENABLED

SQL> select index_name,status from user_indexes where table_name = 'NEWNAME_TT';


INDEX_NAME                     STATUS
------------------------------ --------
T_IND                          VALID

SQL>
SQL> select constraint_name,status from user_constraints where table_name = 'T';


no rows selected

SQL> select index_name,status from user_indexes where table_name = 'T';

no rows selected

[Updated on: Wed, 28 March 2007 11:11]

Report message to a moderator

Re: Rename table [message #227567 is a reply to message #227547] Wed, 28 March 2007 12:01 Go to previous messageGo to next message
marks20101
Messages: 74
Registered: May 2005
Member
Thanks Mohammad, but I know how to look for the constraints and indexes, but I guess my question is if I rename a table this will effect the PK, so should I drop it and then once the tbl is renamed then create the PK and indexes? Or is there any other methods like to modify the PK?


Thanks again for your help,
Mark S
Re: Rename table [message #227576 is a reply to message #227567] Wed, 28 March 2007 12:36 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Taj showed you that when renaming a table, the constraints and indexes are properly handled.
Procedures, functions and views however will have to be changed.
Re: Rename table [message #227580 is a reply to message #227547] Wed, 28 March 2007 12:53 Go to previous messageGo to next message
marks20101
Messages: 74
Registered: May 2005
Member
Got it! Thanks

[Updated on: Wed, 28 March 2007 13:34]

Report message to a moderator

Re: Rename table [message #228264 is a reply to message #227547] Mon, 02 April 2007 05:50 Go to previous message
babai_74_cal
Messages: 6
Registered: April 2007
Location: Kolkata
Junior Member
You can rename the table and there is no problem for handling the Primary key or Indexes. But one way is to rename a table is first you should disable the constraint and indexes and then Rename the table. After renaming it enable the constraint and indexes.
Previous Topic: EXPORT using QUERY
Next Topic: Database Down after Server Reboot
Goto Forum:
  


Current Time: Tue Dec 06 06:51:35 CST 2016

Total time taken to generate the page: 0.11127 seconds