generate unique primary key

[paradox814]

i want to generate a unique primary key, something similar to the auto increment feature, but i do not want it to be auto increment.

The reasoning for this is: this table will have rows deleted by the hundreds every day, and maybe even thousands in a few years (I hope!), All the while having more rows replace the ones that were erased. Is there a way to have mysql create a unique key that is just randomly generated?

Thanks in advance!

[Troy]

Just use the auto number anyway. It doesn't matter if you delete thousands of records. Gaps in the number sequence are not a problem. It's still a unique id.

[paradox814]

Just use the auto number anyway. It doesn't matter if you delete thousands of records. Gaps in the number sequence are not a problem. It's still a unique id.

is there a way to INSERT AND SELECT unique_id at the same time? or does it have to be done as two seperate queries

[Troy]

The most common scenario is you insert into a table, then you immediately need that new ID to insert into a child table. If this is your case, and you only have one secondary insert (or update) to make, then you can just perform your two queries like so:

MySQL example from mysql.com (in the second insert, the ID is inserted in another table):

INSERT INTO foo (auto,text)VALUES(NULL,'text');
INSERT INTO foo2 (id,text)VALUES(LAST_INSERT_ID(),'text');

If you need that id for other purposes, then you have two choices. You can select it out using a query to SELECT LAST_INSERT_ID(), or you can use PHP's function:

mysql_query("INSERT INTO mytable (product) values ('SnippetEdit')");
$last_id = mysql_insert_id();

[trafficman]

With INSERT ... SELECT, you can quickly insert many rows into a table from one or many tables.

For example:

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

[jwshepherd]

you may also manage the uniqueness from the application.
set the field as char and write your own unique id so that you will know what it is every time before and after you submit.

[Troy]

All the answers posted are correct, but I think you are still better off to allow MySql's auto-number feature to manage your unique id key. Make sure you set the id field type to BIGINT.

Like I said before, gaps in the sequence do not matter. Auto-number's purpose is not to maintain a nice sequence of id's but rather to maintain a unique id.

As mentioned by another poster, you can always generate your own unique id within your code, and use that when inserting new records, but that increases the chance of hurting data integrity. Especially if you have a large application with multiple places in the code the table is inserted into. Database systems started implementing methods to manage unique ID's and relational integrity because it's too easy for us programmers to screw it up if we try to manage it in our code! :)