We just argued that storing timestamp in sqlserver in microsoft does guarantee uniqueness, but im a little bit doubful about mysql/mariadb, because we are using parallel execution when inserting to the mysql/mariadb database.

So, Is using timestamp as primary key good idea - mysql/mariadb?

PS: There is always an PK and status but its important if we are using timestamp as the where clause in getting the actual remaining records to be archived/removed (so that it is efficient in querying the records).So its important its unique

  • Please provide the reference about SQL Server. I am very dubious. – Rick James Mar 31 '17 at 3:48
  • I would not trust even microsecond-resolution to be unique. Might work on a single machine today, but what about tomorrow's machine(s), etc. – Rick James Mar 31 '17 at 3:51
up vote 2 down vote accepted

IMO it's bad because of several reasons. First, blindly assigning a generated p.k. for every relation is bad practice. At least try to identify what candidate keys there are in each relation. A surregate p.k. may be the smartest choice in some situations, but it is definitely not so in all possible situations. Good properties for candidate keys are:

  • Unique
  • Irreducible
  • Non-complex
  • Stable
  • Familiar

Often there is a conflict between these criteria and you have to sacrifice one for another.

Second, if the best choice is a surrogate identifier I'll say that timestamp is a poor choice. Especially in MySQL/MariaDB before (MySQL) 5.6.4 where the resolution is seconds. Using a timestamp as a p.k in these DBMS means that you cannot insert more than one row / second (as long as the timestamp should reflect the time of insert).

  • are you considering the timestamp data type in mysql/mariadb right? timestamp is a poor choice is also my answer too thanks for enlightening me, – david Mar 28 '17 at 9:05
  • Yes, it was the timestamp I had in mind. In MySQL/MariaDB timestamp is not the same type as in other DBMS:s, it deviates from the standard in several aspects. Choosing another datetime type does not help either since the maximum resolution is second there as well. – Lennart Mar 28 '17 at 10:14

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.