How to set an existing database field to auto increment

Question

I have an existing database that has an id field that is an unsigned integer with a length of ten (and is set as the primary key). I would like to write an update to change this field so that it auto increments. How would I go about doing that? I am familiar with writing updates, just haven't done one like this before. I am doing this with Drupal 7.

Answer 1

I found the solution I was looking for here. Below is the relevent part.

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('foo');
db_change_field('foo', 'bar', 'bar',
  array('type' => 'serial', 'not null' => TRUE),
  array('primary key' => array('bar')));

Answer 2

Although not strictly a Drupal question, you can make the field into an auto_increment field in MySQL with:

ALTER TABLE my_table MODIFY COLUMN my_id int(10) AUTO_INCREMENT PRIMARY KEY;

"PRIMARY KEY" is just-in-case and may not be needed as is already the primary key.

As with any direct database schema changes, backup first.