MyISAM has this really cool feature where you can specify autoincrement
on a secondary column in a multiple column index. In such a case the
generated value for the autoincrement column is calculated as
MAX(autoincrement column) + 1 WHERE prefix='given-prefix'. For more
refer to
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html. This
is exactly what I want, however I have an InnoDB table so this will not
work (in an InnoDB table, you cannot specify autoincrement on a
secondary column). So what I wanted to know was if there is some easy
way to mimic that behavior. In my last mail that I sent, in order to
mimic that functionality on InnoDB, I had to write several statements
that possibly need to be a part of a transaction. Of course that also
meant that I cannot specify that column as an autoincrement but instead
specify it something as INT NOT NULL.
Thanks
Aveek
Johan De Meersman wrote:
> On Thu, Apr 22, 2010 at 12:09 PM, Aveek Misra <aveekm@stripped> wrote:
>
>
>> I am not sure I understand. If I make the autoincrement column as part of
>> the primary key as (rev + cluster + file), how do I ensure that a reset of
>> the revision number is done as soon as (cluster + file) combination changes?
>>
>>
>
>
> You want the autoincrement to go up every time you change the values in your
> primary key ?
>
> Aside from it not being a recommended practice, to put it mildly, that you
> update primary key values (possible referential inconsistency), I'm not
> aware of this behaviour in MyISAM, either.
>
> An autoincrement is assigned if, and only if you assign NULL or (zero) to an
> autoincrement column during an insert.
>
> If your application behaves differently, it is probably already done either
> in the application, or possibly through the use of triggers.
>
>
>
>
