List:General Discussion« Previous MessageNext Message »
From:Aveek Misra Date:April 22 2010 10:37am
Subject:Re: Auto Increment in InnoDB
View as plain text  
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.
>
>
>
>   

Thread
Auto Increment in InnoDBAveek Misra22 Apr
  • Re: Auto Increment in InnoDBJohan De Meersman22 Apr
    • Re: Auto Increment in InnoDBAveek Misra22 Apr
      • Re: Auto Increment in InnoDBJohan De Meersman22 Apr
        • Re: Auto Increment in InnoDBAveek Misra22 Apr
          • Re: Auto Increment in InnoDBJohan De Meersman22 Apr
            • Re: Auto Increment in InnoDBAveek Misra22 Apr
              • Re: Auto Increment in InnoDBJohan De Meersman22 Apr
            • Re: Auto Increment in InnoDBCarsten Pedersen22 Apr
              • Re: Auto Increment in InnoDBJohan De Meersman22 Apr
                • Re: Auto Increment in InnoDBChris W22 Apr
                  • Re: Auto Increment in InnoDBJohan De Meersman23 Apr
  • Re: Auto Increment in InnoDBmos22 Apr