0

i'm trying to set up a MySQL database for storing biological data. I have to extract this data from a file and i have a perl script for that. The problem i have is that i need three primary keys in order for them to be unique, and i want one of them to be an auto increment integer. I would like, however, the auto-incremented value to reset each time the combination of the first two keys changes.

sequence1 | hit1      | 1
sequence1 | hit1      | 2
sequence1 | hit2      | 1
sequence2 | something | 1
sequence2 | something | 2
sequence2 | something | 3
sequence3 | something | 1

etc. etc.

is that possible or do i have to implement that directly into the script?

thank you

Tito Candelli
  • 11
  • When you reset AI primary key column, it will not be unique anymore for existing values.You can make trigger to manage some other column value, but not AI. – rkosegi Apr 13 '12 at 08:51
  • but the overall key for the table is the combination of the three primary key. That is maintained unique in this configuration. – Tito Candelli Apr 13 '12 at 09:04
  • mm i do not understand what that means, sry i'm not an expert on SQL :( – Tito Candelli Apr 13 '12 at 09:49

1 Answers1

0

It is possible with MyISAM tables only and will not work in InnoDB or any other storage engine MySQL has.

Just create a primary key on (col1, col2, id) and set auto_increment flag on id column. And make sure there is no unique constraint on id alone. MyISAM will generate a new sequence of values per each unique pair of (col1, col2).

Mushu
  • 271
  • 1
  • 5