renaming indexes

Posted in 2005

Topics: Triggers, Constraints & Referential Integrity

KENNETH PENZA — Thu, 06 Jan 2005 12:27:33 — source: IIUG Forums & Mailing Lists

People, We have some tables that have primary and foreign keys and we want to rename the index name to something meaning full. How can we do the rename without dropping and creating the tables. Regards Kenneth

↪ replying to KENNETH PENZA

Uri Haham — Thu, 06 Jan 2005 13:52:44 — source: IIUG Forums & Mailing Lists

Hi You have to drop the primary or foreign key, create index on the same column (unique for primary) and then recreate the primary/foreign key. It will use the Index. you can put the indexes in deferent dbspace or even partition them ! Uri -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of KENNETH PENZA Sent: Thursday, January 06, 2005 2:28 PM To: ids@iiug.org Subject: renaming indexes [3962] People, We have some tables that have primary and foreign keys and we want to rename the index name to something meaning full. How can we do the rename without dropping and creating the tables. Regards Kenneth *********************************************** This Mail Was Scanned By Mail-seCure System in Matrix Herzeliya ***********************************************

↪ replying to KENNETH PENZA

Obnoxio The Chav — Thu, 06 Jan 2005 14:20:31 — source: IIUG Forums & Mailing Lists

KENNETH PENZA said: > People, > > We have some tables that have primary and foreign keys and we want to > rename the index name to something meaning full. How can we do the rename > without dropping and creating the tables. IDS 9.40 has a RENAME INDEX command. Don't know about other versions, but you can try the old RTFM trick. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco I went to the airport to check in and they asked what I did because I looked like a terrorist. I said I was a comedian. They said, "Say something funny then." I told them I had just graduated from flying school. -- Ahmed Ahmed

↪ replying to KENNETH PENZA

Tomchik, Kate — Thu, 06 Jan 2005 15:05:51 — source: IIUG Forums & Mailing Lists

Drop constraint foreign key..... Drop index {old foreign key name} Drop constraint primary key ... Drop index {old primary key name} Create unique index (new primary key name} Create index { new foreign key name} Alter table {primary name} add constraint {primary key} Alter table {foreign name} add constraint {foreign key} This is the logic. You need to go research the syntax. -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of KENNETH PENZA Sent: Thursday, January 06, 2005 7:28 AM To: ids@iiug.org Subject: renaming indexes [3962] People, We have some tables that have primary and foreign keys and we want to rename the index name to something meaning full. How can we do the rename without dropping and creating the tables. Regards Kenneth

↪ replying to KENNETH PENZA

Schmitz, Rob B [IBM Contractor for Sprint] — Thu, 06 Jan 2005 22:04:56 — source: IIUG Forums & Mailing Lists

Kenneth, The "rename index" command has been available since at least 7.3. The constraints are not tied to the name of the index. They are tied to the key. You don't have to drop any indexes or constraints. The only caveat is that you need exclusive access to the table for the fraction of a second it takes to rename the index. Rob Schmitz 913-315-4183 Rob.B.Schmitz@mail.sprint.com -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of KENNETH PENZA Sent: Thursday, January 06, 2005 6:28 AM To: ids@iiug.org Subject: renaming indexes [3962] People, We have some tables that have primary and foreign keys and we want to rename the index name to something meaning full. How can we do the rename without dropping and creating the tables. Regards Kenneth