List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:January 2 2007 4:19pm
Subject:Re: sequences and auto_increment
View as plain text  

> > 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,
> 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,
> this means I need to insert a record before in such table to get the new
> and then insert in my competenza table. This would require a transaction
> will result even more complex than the above, I guess.
> Any other idea?

Well, transactions are never a bad idea :-)

What I would suggest to simulate sequences, is a Stored Function
that returns the new value. Then use that function to retrieve the new
value beforehand and use that when creating a new row in your table.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
My thoughts:
Database development questions? Check the forum!

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