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
Luca Ferrari wrote:
> On Tuesday 02 January 2007 16:51 Brent Baisley's cat, walking on the keyboard,
>> 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
> 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?