0

I've got a table for messages in my mysql-database. And I want to set the auto_increment in dependence on another row, like so:

conversation_id | message_id | content
123                         a_i: 1              bla
123                         a_i: 2              bla
123                         a_i: 3              bla
1234                       a_i: 1              bla

Do you have any idea? Thanks, Kevin.

KevinHKlein
  • 41
  • 1
  • 2
  • Check [this SO question](http://stackoverflow.com/questions/14418901/table-cant-have-2-field-primary-key-with-auto-increment). – inhan Jan 21 '13 at 17:11

2 Answers2

1

Yea, thats not possible with MySQL, at least not with InnoDB. You can instead:

  1. Compute the value manually
  2. Use a trigger to compute and set the value

It is available in ISAM, with some conditions (you need a compound primary key).

Perception
  • 79,279
  • 19
  • 185
  • 195
  • You're right that this isn't possible with MySQL for a table using the InnoDB engine; but it is possible using the MyISAM engine. – spencer7593 Jan 21 '13 at 17:28
0

That feature is available only with MyISAM, and not InnoDB.

<snip>

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

</snip>

http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html

Again, this only works if the table uses the MyISAM engine. You'd need to ensure there was no index with message_id as a leading column, and ensure you had an index:

ON mytable (conversation_id, message_id)
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Hey, thanks for your Answers. But I don't know, what to do right niow. I converted the messages table to MyISAM, but what did I got to do now? The names of the columns are exactly the same as above. – KevinHKlein Jan 21 '13 at 18:01
  • `CREATE UNIQUE INDEX mytable_UX1 ON mytable (conversation_id, message_id)`, and add `AUTO_INCREMENT` specification on the message_id column. Get the current column specification using `SHOW CREATE TABLE mytable`, and then `ALTER TABLE mytable CHANGE message_id message_id ... AUTO_INCREMENT` (just replace the ... with the current column attributes (INT UNSIGNED NOT NULL whatever it currently is.) Also verify that there are no indexes with `message_id` as the leading column. – spencer7593 Jan 21 '13 at 18:05