Page 1 of 1

Is ok to use datetime as primary key in MySQL?

Posted: Wed Mar 26, 2008 6:36 am
by BornForCode
As i know MS SQL keeps the date in float so making an index on such field is not a problem, i would like to know if the same physical representation of datetime is applied in MySQL case.

Can you please provide me some details about this? I tried to find the information in MySQL doc but it was impossible.

Re: Is ok to use datetime as primary key in MySQL?

Posted: Wed Mar 26, 2008 7:25 am
by onion2k
Not something I'd do. MySQL stores a datetime as two sets of 4 byte integers in a packed format ... years*10000 + months*100 + days and hours*10000 + minutes*100 + seconds. If you were certain you'd never need to store two records with the same key then it would work, but in my experience no matter how certain you are now whatever it is will throw up a problem in the future.

Re: Is ok to use datetime as primary key in MySQL?

Posted: Wed Mar 26, 2008 9:51 am
by Inkyskin
Agreed, the PK should really be as unique as humanly possible - and with a field thats also used as a variable, you could easily get messed up. I'd stick with an auto incrementing standard integer.

Re: Is ok to use datetime as primary key in MySQL?

Posted: Wed Mar 26, 2008 10:33 am
by pickle
It's OK to use if you are sure you want to have the restriction of only having at most one entry per second. If the time needs to be unique, making it the primary key is alright. If, though, you just want a unique key per row, do as ~Inkyskin says & make it an auto incrementing integer column.

Re: Is ok to use datetime as primary key in MySQL?

Posted: Thu Mar 27, 2008 8:48 am
by BornForCode
The table is an aggregation one and i won't be concurrent inserts or so on, the finest value that i need is up to hour (not minutes and secs).

If i make a pk using an auto_increment i will have to create unique on the date column to and this is somehow redundant :roll:

Re: Is ok to use datetime as primary key in MySQL?

Posted: Thu Mar 27, 2008 9:51 am
by pickle
Redundancy can be a good thing. Your currently relying on best practice or a cron job to ensure your data makes sense. Why not put a hard rule in the database (the primary key) to ensure data integrity? If you're only writing once an hour, there won't be any serious write delays due to indexing, and your reads will be a bit faster.