Sequence problem

[Dan]

Hello there.

I have recently been tasked with creating a mySql database. I am much
more used to using Oracle, so I guess my problem is translating Oracle
into mySql. My current problem is the auto-increment. In oracle, I
would never do such a thing, I would instead use a sequence. However,
when I try creating a sequence in mySql I get the following error:
create sequence test;

ERROR 1064: You have an error in your SQL syntax. Check the manual
that corresponds to your MySQL server version for the right syntax to
use near 'sequence test' at line 1
Now, I would be more than happy to use the auto-increment feature if I
could be guarenteed a way to retrieve the next sequence for that
session. Since mySql isn't transaction based, and I have hundreds of
users making updates to the table, I don't see a way to guarentee that
I will be getting the correct ID for the insert I am about to do so
that I can use it as a foreign key in another table. I hope this
makes sense.

If anyone can shed some light on this I would be very grateful.
Please let me know if I am tackiling this problem incorrectly.

Thanks
Dan

[Aggro]

Dan wrote:

Now, I would be more than happy to use the auto-increment feature if I
could be guarenteed a way to retrieve the next sequence for that
session. Since mySql isn't transaction based, and I have hundreds of
users making updates to the table, I don't see a way to guarentee that
I will be getting the correct ID for the insert I am about to do so
that I can use it as a foreign key in another table.

http://www.mysql.com/doc/en/Getting_unique_ID.html

"The most recently generated ID is maintained in the server on a
per-connection basis. It will not be changed by another client. It will
not even be changed if you update another AUTO_INCREMENT column with a
non-magic value (that is, a value that is not NULL and not 0)."

[Aggro]

Dan wrote:

Now, I would be more than happy to use the auto-increment feature if I
could be guarenteed a way to retrieve the next sequence for that
session. Since mySql isn't transaction based, and I have hundreds of
users making updates to the table, I don't see a way to guarentee that
I will be getting the correct ID for the insert I am about to do so
that I can use it as a foreign key in another table.

http://www.mysql.com/doc/en/Getting_unique_ID.html

"The most recently generated ID is maintained in the server on a
per-connection basis. It will not be changed by another client. It will
not even be changed if you update another AUTO_INCREMENT column with a
non-magic value (that is, a value that is not NULL and not 0)."