generating unique id for tuples in a database

saqib

Hi,
I am working on a complex database application that has multiple relations. Right now I am using mySQL. And I am also using auto_increment for the unique ID for each table. How I would prefer not to use auto_increment.

I would like a external program give me unique IDs and manage all the IDs for me as well. I would like the application to be database independent, i.e. it should work on Oracle etc as well.

Any suggestions?

Thanks
Saqib Ali
http://docbook.sc-icc.org

Weedpacket

Depends on what you mean by "manage the IDs". Generating them in PHP is trivial with [man]uniqid[/man]. Or maybe just use a [man]microtime[/man]-generated timestamp.

swr

I would continue to use auto_increment. Abstract the database-specific part out by having a function to return the ID from the last insert. That is the approach I've seen used in ezsql.php (a very nice little class; an article about it was posted on phpbuilder.com a while back).

With mysql, mysql_insert_id() does the job. With MSSQL (identity column), "select @@identity" does it. With PostgreSQL (SERIAL data type, which just creates a sequence generator and column default value) it's a little more complex: "select currval('<table><column>seq')" (too bad ezsql.php doesn't do this). I haven't used Oracle, but I'd bet it has a mechanism.

auto_increment (and MSSQL "identity", and PGSQL "serial") exist exactly for the purpose of generating unique values for primary key columns. Better to use them than try to invent something else.

Duey

you could try just generating a random number.
$id = round(rand(0,1000000000));
but theres all ways a change that the same number turns up twice.
or you could do this
$id = md5(rand(0,9999999999));
$id = substr("$id", 15);
this would create a 15 charator id pretty much totally unique.(change the number 15 to anything less then 35)

swr

Originally posted by Duey
$id = md5(rand(0,9999999999));
$id = substr("$id", 15);
this would create a 15 charator id pretty much totally unique.(change the number 15 to anything less then 35) [/B]

No, that is no better than just using $id = rand(0,9999999999);

In your example there are only 10 billion possible inputs to the md5 function, so there can not be more than 10 billion possible outputs. The fact that md5 could produce more possible outputs if you had more possible inputs doesn't help you, because you don't have more possible inputs.

Also, because rand() is implemented with 32-bit integers on most systems you will be limited to at most 4.something billion possible outputs, rather than the 10 billion your 9999999999 suggests.

Even worse, a 32-bit rand() gives you 50% chance of a collision after just 2¹⁶ (65536) records, due to birthday paradox.

Some comments in the PHP manual for rand() (http://www.php.net/manual/en/function.rand.php) suggest that Windows uses 16-bit integers for it. If true, that's even worse: 50% chance of a collision after just 2⁸ (256) records.

Bottom line: Don't use rand() for generating unique numbers. It wasn't designed for that.

Duey

hmm it can be used for that eg.
$id = rand(0,99999)/rand(0,999999);
you could just keep reducing the chance of collision

stick_figure

Or you could just SELECT stuff from the table and make sure that it doesn't already exist and make another...