As reported in DatabaseSchema_pgsql::changeField, and in db_change_field():
IMPORTANT NOTE: To maintain database portability, you have to explicitly recreate all indices and primary keys that are using the changed field.
That means that you have to drop all affected keys and indexes with db_drop_{primary_key,unique_key,index}() before calling db_change_field(). To recreate the keys and indices, pass the key definitions as the optional $new_keys argument directly to db_change_field().
For example, suppose you have:
$schema['foo'] = array(
'fields' => array(
'bar' => array('type' => 'int', 'not null' => TRUE)
),
'primary key' => array('bar')
);
and you want to change foo.bar to be type serial, leaving it as the primary key. The correct sequence is:
db_drop_primary_key($ret, 'foo');
db_change_field($ret, 'foo', 'bar', 'bar',
array('type' => 'serial', 'not null' => TRUE),
array('primary key' => array('bar'))
);
Similar code is reported for Drupal 7.
Keep in mind that, for my experience, you cannot remove a primary key that uses a serial field. On Drupal 6, I got an error all times I tried doing that; I didn't try that on Drupal 7.
Apart that, I don't know any other issue you could have with database indexes.
About adding an index to a database table that is created from another module, I would not suggest doing that, because:
- A module doesn't drop an index for a field that is being changed, if the module itself didn't create that index. It would not be possible for the module to do that because it doesn't know the name of the index.
- Modifying a database table created by another module is never a good idea, even in the case the module is a core module. If there is another module that changes the same table, how could the modules handle any conflict they have with each other, or with the changes the core module would apply to its own database?
If the database table is being created from another module (a core module or a third-party module), I would suggest to open a feature request for the module, providing a use case for using a new index; if there are any performance issue, adding an index could be the desired thing to do.
If you are going to add an index to a table created from another module to your own site, then be prepared to any changes you need to do to your custom module every time the module is updated, and before you install it in your own site.
It is you who can decide if the extra work is worth the performance you get. Personally, I don't think it is worth, though.
