Excuse-me, but what's the usefulness of this ?

This slow & awkward technique has been known for decades and is the only way in Oracle to have what MySql always had, ie auto_increment primary keys.

So what's this useful for ? for auto_incrementing a non-pri-key column ?

Regards

VGR

---------------------
www.europeanexperts.org

#1 expert at PHP & MySql on www.experts-exchange.com before I left in Oct 2003 ;-)
I left my place for "VB" Mike Hillyer to take it ;-)

User: arjen_lentz Date: 2005-08-03 00:17 (UTC) Subject: Re: what is the usefulness of this ?

For me, I was just playing with some new trigger and stored proc functionality. In that sense, this was a nice demo ;-)

I agree that for most purposes, AUTO_INCREMENT does fine. But... sometimes people want other things. Sequences are more programmable than auto-inc, so if you want to do for instance funny increments, that is also possible. You can also can have a single sequence apply to multiple tables, and people sometimes want that.

In the case of migration, apps sometimes rely on certain weird behaviour. When migrating, you could use something like the above to reproduce the same result and keep the app happy.

But really, primarily, it was just a little exercise.

I see a second use for this. I have four different entities, each of which must use a unique primary key. The key must be unique across the different entities. Each entity is described, physically, in its own table. However, since there are four different tables, I can't use an auto_increment column in each table, b/c the key won't be unique across the tables (for those who are interested, these entities are derived from a base entity, so they cannot efficiently all be stored in one table). How to generate keys that are unique across all four derived tables? A sequence.

User: arjen_lentz Date: 2005-12-14 14:00 (UTC) Subject: Re: what is the usefulness of this ?

That kinda bites general design principles.
The normal way to handle such a situation is:
Only one table has an auto-inc PK, the others have a foreign key to that first table.
You first insert into the first table, get the assigned id - using mysql_insert_d() or SELECT LAST_INSERT_ID() - and then insert into the other tables.
Easy.

User:	kkirov
Date:	2005-08-03 04:01 (UTC)
Subject:	Not expected behavior

Your method produced on my MySQL 5.0.10 instance these results:

mysql> SELECT * FROM data;
+----+------+
| id | info |
+----+------+
| 0 | bla |
| 2 | foo |
| 3 | bar |
+----+------+

The first '0' is wrong. It appears because the first call of nextval() returns 0, because
the first INSERT doesn't produce duplicate key error, and LAST_INSEERT_ID isn't called. So
LAST_INSERT_ID() returns 0, and RETURN gives you zero as well.

To produce a correct results I suggest to use this function:

CREATE FUNCTION nextval (seqname CHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT INTO sequences VALUES (seqname,1)
ON DUPLICATE KEY UPDATE val=LAST_INSERT_ID(val+1);
RETURN IF(LAST_INSERT_ID()=0,1,LAST_INSERT_ID());
END

User: arjen_lentz Date: 2005-08-03 04:47 (UTC) Subject: Re: Not expected behavior

Hey, right you are. LAST_INSERT_ID() isn't set by the INSERT because there's no AUTO_INCREMENT column. Silly me!

This should also work:

INSERT INTO sequences VALUES (seqname,LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE val=LAST_INSERT_ID(val+1);

I m very new to MqSql, The above thing is very nice, it took just 5 to 10 minute for me to do this successfully.
My sincere thanks for this..
Keep going..

Nice workaround here. As a MySQL newcomer, I am finding alternative ways to come up w/ the closest "sequence"-like object as an Oracle developer/DBA.

Regards!
colds01@yahoo.com