0

I'm using IBExpert to operate my databases. I have an old table with two fields:

 - code (char 3)  PK + INDEX
 - name (varchar 30)

The new table should be

 - code (integer) PK + INDEX
 - name (varchar 30)

The data in the code column consist of only numbers even if it was a char type.

Is it possible to change the type of the code column? If yes how? I assume that I need to drop the primary key and delete or deactivate the index, but IBExpert won't let me do it and gives me this error when I try to drop the PK: object INDEX is in use

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
VirussInside
  • 187
  • 17
  • 1
    See Tools / Analyze Dependencies in IBExpert, probably this index is used by some other object but PK ( unique constraints, foreign key constrains, etc). Dumb option would be Tools / Export Metadata into text file, then loading this huge SQL script into any programmers editor and use simple text search for that index name. Also, Tools / Search Metadata can help hopefully. – Arioch 'The Jun 02 '21 at 13:15
  • 1
    Also, i am really not sure this type change from string to number would really work. Even if it would - you would better instantly do update all over the table after this: `update tablename set code = 0 + cast( code as integer)` to make all the records converted to new format. The proper way would actually be creating new column, then populating it, then dropping old column, then renaming the new one. Which would be troublesome if old column is used by other database objects, which seem to be the case – Arioch 'The Jun 02 '21 at 13:19
  • @Arioch'The Thanks for your help, seems like a problem of dependencies. – VirussInside Jun 02 '21 at 13:20
  • ...and since there is no concept of "invalid thus not-instantiated" dependency in FB you might be up to a rough ride of unrolling the onion. Sometimes it is easier to create EMPTY database from Extract Metadata script and then pump the data into the new empty shell – Arioch 'The Jun 02 '21 at 13:21
  • 1
    Also, even existing CONNECTIONS to a database might be using some metadata objects, depending on transactions settings (wait/nowait, AFAIR). That at least was case with stored procedures, not sure about PKs and Indices. But an active `SELECT` can certainly be using some index, agree? – Arioch 'The Jun 02 '21 at 13:23
  • Also, you would probably be better with FAQs and community at https://www.sql.ru/forum/interbase and at http://ibase.ru – Arioch 'The Jun 02 '21 at 13:25

1 Answers1

1

The error object INDEX is in use means exactly that: the object is in use, for example in the query plan of a prepared statement. To be able to modify it, you'll need to ensure nothing is holding an existence lock on that object, and the simplest way to do that is to shutdown the database using gfix, make your changes and set your database online again.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197