List:General Discussion« Previous MessageNext Message »
From:Ken Brown Date:January 2 2007 5:33pm
Subject:Re: sequences and auto_increment
View as plain text  
I had a similar problem a while ago and I got round it by using a trigger -

called a function on an insert to
read a value from a table (use for update when selecting)
update with incremented value
then use the return value from the ffunction to "set" the new value - 
you can do all your login or your primary key in it.

Not at a machine where I have the code handy and not sure if mysql has 
implemented an autonomous pragma for this kind of thing yet but select 
for update should deal with things as long as you keep the function tight

Ken

Luca Ferrari wrote:
> On Tuesday 02 January 2007 16:51 Brent Baisley's cat, walking on the keyboard, 
> wrote:
>
>   
>> CREATE TABLE competenza (
>> competenza varchar(30) NOT NULL default 'comp-06-',
>> id_competenza int unsigned not null auto_increment,
>> descrizione varchar(100),
>> PRIMARY KEY (competenza, id_competenza)
>> )
>>
>> Since your PRIMARY KEY is a combination of 2 fields (competenza +
>> id_competenza ), each competenza value will have it's own auto increment
>> (id_competenza ) sequence. So id_competenza  won't be unique, but the
>> combination of competenza + id_competenza will be.
>>
>>     
>
>
> Thanks for your suggestion, but this would make my queries more complex, since 
> to get the id of a skill (italian is competenza) will require querying two 
> fields.
> It's true that I can simulate sequences with a table with auto_increment, but 
> this means I need to insert a record before in such table to get the new id 
> and then insert in my competenza table. This would require a transaction and 
> will result even more complex than the above, I guess.
>
> Any other idea?
> Thanks,
> Luca
>
>   


Thread
sequences and auto_incrementLuca Ferrari2 Jan
  • Re: sequences and auto_incrementMartijn Tonies2 Jan
  • Re: sequences and auto_incrementBrent Baisley2 Jan
    • Re: sequences and auto_incrementLuca Ferrari2 Jan
      • Re: sequences and auto_incrementKen Brown2 Jan
  • Re: sequences and auto_incrementMartijn Tonies2 Jan