Rename Index?
Hi all:
Environment:
Solaris 7. Sun E-450 dual 400 Mhz processors.
INFORMIX-SQL Version 6.02.UC1
INFORMIX-ESQL Version 7.11.UC1
INFORMIX-OnLine Version 7.11.UC1
I need to rename a table, together with its indexes.
There is a 'RENAME TABLE' statement in SQL, but there's
no 'RENAME INDEX' equivalent for indexes. Is there a
way to rename indexes without dropping and re-creating them?
Performing an UPDATE to sysindexes is out of the question
since I need this functionality in ESQL/C as a regular
user.
Example:
create table table1
(
itemcode char(3),
itemdesc char(32)
);
create unique index ixtable1_1 on table1 (itemcode);
rename table table1 to table2;
%
% Output of "dbschema -d $DBNAME -t table2" gives:
%
create table table2
(
itemcode char(3),
itemdesc char(32)
);
revoke all on table2 from "public";
create unique index ixtable1_1 on table2 (itemcode); ^^^^^^^^^^
I need to rename ixtable1_1 to ixtable2_1 as well.
Any solutions? Thanks!!!
seejah!
...eric
Eric Lorenzo Lim wrote: > Hi all: > > Environment: > Solaris 7. Sun E-450 dual 400 Mhz processors. > INFORMIX-SQL Version 6.02.UC1 > INFORMIX-ESQL Version 7.11.UC1 > INFORMIX-OnLine Version 7.11.UC1 > > I need to rename a table, together with its indexes. > There is a 'RENAME TABLE' statement in SQL, but there's > no 'RENAME INDEX' equivalent for indexes. Is there a > way to rename indexes without dropping and re-creating them? No. You will have to drop and recreate the indexes with new names. > Performing an UPDATE to sysindexes is out of the question > since I need this functionality in ESQL/C as a regular > user. You don't want to manually muck with the system catalog tables anyway, except for a VERY FEW safe columns. Idxname is NOT such a safe column FAIK. Sorry, this is one of those times when the answer is: You have to do it the hard way. Art S. Kagel
Eric Lorenzo Lim wrote:
> Environment:
> Solaris 7. Sun E-450 dual 400 Mhz processors.
> INFORMIX-SQL Version 6.02.UC1
> INFORMIX-ESQL Version 7.11.UC1
> INFORMIX-OnLine Version 7.11.UC1
>
> I need to rename a table, together with its indexes.
> There is a 'RENAME TABLE' statement in SQL, but there's
> no 'RENAME INDEX' equivalent for indexes. Is there a
> way to rename indexes without dropping and re-creating them?
>
> Performing an UPDATE to sysindexes is out of the question
> since I need this functionality in ESQL/C as a regular
> user.
>
> Example:
>
> create table table1
> (
> itemcode char(3),
> itemdesc char(32)
> );
> create unique index ixtable1_1 on table1 (itemcode);>
> rename table table1 to table2;
>
> %
> % Output of "dbschema -d $DBNAME -t table2" gives:
> %
> create table table2
> (
> itemcode char(3),
> itemdesc char(32)
> );
> revoke all on table2 from "public";>
> create unique index ixtable1_1 on table2 (itemcode);> ^^^^^^^^^^
>
> I need to rename ixtable1_1 to ixtable2_1 as well.
>
> Any solutions? Thanks!!!
As Art said, "No". I'll just observe that I put in a feature request,
oh, 5-10 years ago saying that for every object in the database with a
name, there should be a RENAME command. They've added RENAME DATABASE
which is a step in the right direction; now, how about the rest of the
database -- indexes, constraints, procedures, types, etc.
Of course, my FR went into the FRDB - aka the Black Hole.
Incidentally, I've not used Oracle but I think it has a 'CREATE OR
REPLACE PROCEDURE' statement which could be useful in Informix, too.
Similarly for views, too.
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN
#include <disclaimer.h>
Jonathan Leffler wrote:
> Eric Lorenzo Lim wrote:
>
> > Environment:
> > Solaris 7. Sun E-450 dual 400 Mhz processors.
> > INFORMIX-SQL Version 6.02.UC1
> > INFORMIX-ESQL Version 7.11.UC1
> > INFORMIX-OnLine Version 7.11.UC1
> >
> > I need to rename a table, together with its indexes.
> > There is a 'RENAME TABLE' statement in SQL, but there's
> > no 'RENAME INDEX' equivalent for indexes. Is there a
> > way to rename indexes without dropping and re-creating them?
> >
> > Performing an UPDATE to sysindexes is out of the question
> > since I need this functionality in ESQL/C as a regular
> > user.
> >
> > Example:
> >
> > create table table1
> > (
> > itemcode char(3),
> > itemdesc char(32)
> > );
> > create unique index ixtable1_1 on table1 (itemcode);> >
> > rename table table1 to table2;
> >
> > %
> > % Output of "dbschema -d $DBNAME -t table2" gives:
> > %
> > create table table2
> > (
> > itemcode char(3),
> > itemdesc char(32)
> > );
> > revoke all on table2 from "public";> >
> > create unique index ixtable1_1 on table2 (itemcode);> > ^^^^^^^^^^
> >
> > I need to rename ixtable1_1 to ixtable2_1 as well.
> >
> > Any solutions? Thanks!!!
>
> As Art said, "No". I'll just observe that I put in a feature request,
> oh, 5-10 years ago saying that for every object in the database with a
> name, there should be a RENAME command. They've added RENAME DATABASE
> which is a step in the right direction; now, how about the rest of the
> database -- indexes, constraints, procedures, types, etc.
>
> Of course, my FR went into the FRDB - aka the Black Hole.
>
> Incidentally, I've not used Oracle but I think it has a 'CREATE OR
> REPLACE PROCEDURE' statement which could be useful in Informix, too.
> Similarly for views, too.
>
>
I think we should all vote for Jonathan to be in charge of the future
development of products at Informix.
I personally believe the products would improve that much more, because he
cares.
--
Compliments of QueriX
--------------------------------------------------------------------------------------------------
QueriX 4GL Compilers are Informix 4GL Compatible, and Connection to other
RDBMS such as Oracle.
Hydra 4GL Compiler (Compatible with I4GL) Compile once, run everywhere
Phoenix Windows GUI. (Front End to 4GL)
Chimera Java GUI The only GUI you will ever need... (Front End to 4GL)
Arachne Web Technology (Front End to 4GL on the Web)
For more details visit: http://www.querix.com/
---------------------------------------------------------------------------------------------------