0

I am currently facing issue of renaming an index to a number.

I have tried the below:

RENAME INDEX 584_1973_tmp TO '584_1973';
RENAME INDEX 584_1973_tmp TO ' 584_1973';
RENAME INDEX 584_1973_tmp TO "584_1973";
RENAME INDEX 584_1973_tmp TO " 584_1973";

None of the above seems to work. Is it possible to rename an index to a number within Informix?

3
  • Why would you want to use an index number rather than a name? You could get the double-quoted forms to work if you set DELIMIDENT=1 in the environment, but I'm not convinced it's a good idea, even though you rarely refer to indexes by name. Jul 24, 2019 at 7:57
  • I'm also a bit puzzled — how did you create an index with a name such as 584_1973_tmp in the first place? You'd need to be using delimited identifiers when the index was created. The Informix DBMS automatically creates index names with a leading space when you implicitly create an index (e.g. CREATE TABLE x(y SERIAL NOT NULL PRIMARY KEY, …) because it is hard for users to do that. (See output from SELECT idxname FROM informix.sysindices WHERE idxname MATCHES ' *' for example.) Jul 24, 2019 at 8:03
  • I don't want to use numbering, I want to use proper naming. The reason for this is because I changed the index constraint from a number to a name, but the developers don't want that so I needed to revert the change and realized that it cannot be renamed back to the original. Jul 25, 2019 at 8:03

1 Answer 1

0

I realized that once you rename an index like the below:

RENAME INDEX 584_1973 TO 584_1973_tmp;

You cannot revert, you will need to drop and recreate. If it is a unique index constraint, then it will auto recreate with the original numbering after dropped.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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