List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:January 2 2007 3:51pm
Subject:Re: sequences and auto_increment
View as plain text  
I don't think MySQL has exactly what you are looking for, but you may be able to get the
behavior you want.

The auto_increment value is actually based on an index and doesn't have to be unique. So
you could create a compound index that has 
one or more fields plus the auto_increment field. The effect would be having multiple
sequence numbers.

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.

----- Original Message ----- 
From: "Luca Ferrari" <fluca1978@stripped>
To: <mysql@stripped>
Sent: Tuesday, January 02, 2007 8:54 AM
Subject: sequences and auto_increment

> Hi all,
> I'm new to MySQL coming from PostgreSQL backgroud. I'd like to know how to
> obtain the same effect of a sequence + concat as default value of a table in
> mysql. For example, consider the following table definition:
> CREATE TABLE competenza
> (
>  id_competenza character varying(30) NOT NULL DEFAULT ('comp-06-'::text ||
> (nextval('sequenza_competenza'::regclass))::text),
>  descrizione character varying(100),
>  CONSTRAINT competenza_pkey PRIMARY KEY (id_competenza)
> )
> there, id_competenza is compound by a string "comp-06" and the next value of a
> sequence (similar to auto_increment). In MySQL there're no sequences, or
> better, there's only an auto_increment action on an int field. How can I
> obtain the same effect of the concatenation of a sequence and a string?
> Thanks,
> Luca
> -- 
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

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