Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    9

    Unanswered: TIMESTAMP as primary key ?

    If I need a primary key (PK) that can contain the numbers as large as an INT, I would normally use an INT.

    But the problem with INT is that, if the keys are all used up (exhausted), I need to start thinking of ways to reuse “skipped” or “deleted” primary keys. In other words, I need to fill up the “gaps” so as not to waste them.

    Another problem is that, if the PKs are all used up, I need to have another timestamp field in the same table to tell me which very old record I can delete to make space for the new record that I want to insert. (Assuming that we can delete very old records. Eg a web email account where, say, an account which stayed dormant for 5 years.)

    Since INT has these problems, and since both INT and TIMESTAMP datatypes have a size of 4 bytes, I was thinking, why not just use TIMESTAMP for my PK field instead.

    Besides TIMESTAMP is just an integer internally in MySQL, representing the number of seconds since epoch (1970-1-1 00:00:00). It would be as index-able as an INT? Searching through it will be just as efficient? Using it as foreign keys in multiple tables would give no problem?

    Is using TIMESTAMP as a PK field OK ? Can anyone tell me whether what I am doing/thinking right or wrong ? Any potential problems ?

    Any advice would be very much appreciated.

    DB : MySQL 5.0.16

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    wouldn't it make more sense to change the datatype of the PK to bigint?

    Timestamp might work providing that no one ever changed a record, otherwise the value will change and your realtional integrity will be wrecked if there are any child tables. Equally you can't guaranteee the uniqueness of a timestamp value.

    Changing to Bigint should leave the existing data undisturbed and allow for further growth.

    It may make sense to prune your data in any event, depends on your business rules, but if someome hasn't traded with / used your services for more than say 12 months do you need to keep their data?

  3. #3
    Join Date
    Nov 2005
    Posts
    9
    Timestamp might work providing that no one ever changed a record, otherwise the value will change and your realtional integrity will be wrecked if there are any child tables.
    NOT TRUE. You can make a TIMESTAMP value to stay the same even with a update. Use
    ts_col TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    The column will retain its value for subsequent updates.

    Equally you can't guaranteee the uniqueness of a timestamp value.
    I would be interested to know why.

    It may make sense to prune your data in any event, depends on your business rules, but if someome hasn't traded with / used your services for more than say 12 months do you need to keep their data?
    Good point you have there. Thanks. Yes normally you would keep those valuable info for statistical purposes.

    wouldn't it make more sense to change the datatype of the PK to bigint?
    My point is, since both INT and TIMESTAMP datatypes have a size of 4 bytes. BIGINT is on another level already.

  4. #4
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    Quote Originally Posted by Eng Goh
    I would be interested to know why.
    Why you can't guarantee uniqueness when using a TIMESTAMP as primary key? Well, what happens if you add two records within one second?

    And why do you not just use a BIGINT for PK and have a TIMESTAMP field for the timestamp, if you need one? The primary key does not have to be a value that is suitable for anyone but the database to deal with...

  5. #5
    Join Date
    Nov 2005
    Posts
    9
    Why you can't guarantee uniqueness when using a TIMESTAMP as primary key? Well, what happens if you add two records within one second?
    Oh sorry, I didn't know that all MySQL time columns (TIME, DATETIME, and TIMESTAMP) does not keep milliseconds. Precision is only up to seconds.


    And why do you not just use a BIGINT for PK and have a TIMESTAMP field for the timestamp, if you need one? The primary key does not have to be a value that is suitable for anyone but the database to deal with...
    I was thinking...since I already have a timestamp column...and if it can also act as the primary key, then I save an extra unnecessary BIGINT column which is not small (in size) anyway...

  6. #6
    Join Date
    Jul 2005
    Posts
    63
    Do you think timestamp column as PK does not create gaps thereby making you lose a lot of slots - not to mention you get to start somewhere around the timestamp ID value of NOW instead of starting at 1 ?

  7. #7
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    Quote Originally Posted by Eng Goh
    Oh sorry, I didn't know that all MySQL time columns (TIME, DATETIME, and TIMESTAMP) does not keep milliseconds. Precision is only up to seconds.
    Well, it is still possible that two records are added the same millisecond...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •