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: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
Hi,
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?
Hmm, well, I think: not at all.
Unless you implement something like sequences yourself.
MySQL doesn't allow functions to be used in the DEFAULT clause either.
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
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 [EMAIL PROTECTED]
To: mysql@lists.mysql.com
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: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
Hi, 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? 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 http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sequences or auto_increment column????
Does anybody know which approach is better with mysql: Create sequences for tables or an auto_increment columns??? and why??? Siomara _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sequences or auto_increment column????
I prefer to use Auto_Increment for ease of use. In addition, I don't think that MySQL supports sequences outright (like you may be used to with Oracle). There are some workarounds availabe within the MySQL manual, do a search with sequences. Good Luck, Dennis ** Beridney Computer Services http://www.beridney.com - Original Message - From: Siomara Pantarotto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 27, 2001 10:04 AM Subject: sequences or auto_increment column Does anybody know which approach is better with mysql: Create sequences for tables or an auto_increment columns??? and why??? Siomara - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
