MySQL Number Field Random Default Value?

[Guest_imported]

Folks,

I'm porting some Access databases over to MySQL, but am concerned about the data type of one particular field.

In Access the field is a number (Long Integer) but has a default value that creates a random but unique number, seeded from the time of the host. The default value is therefore:

Int((Time()*1000000)*Rnd())

What this does is give new records a unique ID, together with a regular primary key ID autonumber column.

Is there a way to carry this default value instruction across to MySQL?

Many thanks!


Stuart.

 

[piti]

hi

i'm not sure if this could be set as a default value but your form transfered into mysql would look like this

FLOOR(((CURTIME()+0)*1000000)*RAND())

 

[Guest_imported]

Piti,

Many thanks for the suggestion. I'm using EMS MySQL Manager as a GUI for MySQL and unfortunately, it won't accept the suggested syntax as a default value. Whether that's a problem with MySQL Manager or MySQL itself, I'm not sure.

I could write a random value into the database on the submission of my form using a random number instruction, but then there's no way of making it unique and sooner or later it is bound to produce a 500 error (the field has to be unique).


Stuart.

 

[sleipnir214]

According to the MySQL online documentation: (

http://www.mysql.com/doc/en/Mathematical_functions.html)

"A DEFAULT value has to be a constant, it can not be a function or an expression."

So that's not going to work.

In any regard, the function you've described doesn't make the primary key any more unique than an auto-increment field alone.
______________________________________________________________________
TANSTAAFL!

 

[Guest_imported]

sleipnir214,

Many thanks for the pointer - I'd searched through the MySQL documentation but must have missed that reference entirely.

The database I'm working with already has an auto-increment primary key used for identifying records when updating or deleting, but the randomly generated number is what ties records together across tables. In other words, if the identification number is 123456 then it'll be the same across all tables even if the auto-increment ID numbers associated with those records are different.

As I mentioned before, I can generate this number in the ASP code and write it along with the remainder of the form data, but keeping it unique is then a problem. I suppose running the equivalent of a "if record exists" routine would be a clunky work-around.


Stuart.

 

[Guest_imported]

sleipnir214,

A quick follow-up... I can't actually find the text you quoted at that URL, is that the correct document? Thanks!


Stuart.

 

[sleipnir214]

Sorry, no, that's not the right link. I didn't cut and paste correctly and didn't catch it before I submitted. Here's the right one:

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

. The text of I quoted is a little more than halfway down the page.


Despite what Microsoft says on the topic, there is no real advantage to having context-free database-wide unique identifiers. I can see some advantage to using global UUIDs if you are going to be transferring data from one database to another by methods other than replication, but not if the data is going to stay in one system. Even then, if you are careful, they're nothing more than a convenience.

The column values which associate individual members of one set of data in one table to individual members of another set of data in another table only have meaning within the context of that that single relationship. So making them database-unique or globally unique is superfluous.

I mean, you're not going to search every id column of every table in your database to look for a known id number. You're going to apply context and know that if you are looking for information about users you should look for the known id number in the id column of the "users" table. ______________________________________________________________________
TANSTAAFL!

 

[Guest_imported]

sleipnir214,

Many thanks for the revised link, that's a huge help.

It looks like to retain the random number field I'll need to create it in ASP and then write it to the database, checking to make sure it's unique first. Would you have any suggestions on how best to do that, perhaps by reading the existing data first?


Stuart.