A Table with a timestamp as transaction date and primary key...
I read some articles that the use of SQL's TIMESTAMP in a table is use for recording the actual date/time that the row was inserted and for row(s) that is/are updated. I'm a little troubled by that because I want a table with a timestamp in the first column to be the transaction date which can be done by insert and that the timestamp not be altered with the SQL update of any sort. I also want the TIMESTAMP to be a primary key. So, what is my options? Thanks...
Re: A Table with a timestamp as transaction date and primary key...
Scott Fletcher wrote: I read some articles that the use of SQL's TIMESTAMP in a table is use for recording the actual date/time that the row was inserted and for row(s) that is/are updated. I'm a little troubled by that because I want a table with a timestamp in the first column to be the transaction date which can be done by insert and that the timestamp not be altered with the SQL update of any sort. Then you need 2 timestamps. Both get updated on insert, The first gets updated on updates. I also want the TIMESTAMP to be a primary key. Sorry. This would allow only one insert per second. So, what is my options? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A Table with a timestamp as transaction date and primary key...
TIMESTAMP has a resolution of only 1 second. Not good enough as a primary key for most applications. You may want an auto_incrementing int field (or bigint depending on the size of your data) and make that your primary key. Only the first TIMESTAMP column is automatically updated with the update date. If you want to have a static date/time field you can use a datetime field and populate it with the NOW() function during insert. Unfortunately all default values must be constants or you could set the default of the column to be the NOW() function and not have to ever reference that column in an INSERT statement. The fine manual covers your situation fairly well. I suggest you start at : http://dev.mysql.com/doc/mysql/en/DATETIME.html and follow the links to the rest of the articles describing TIMESTAMPS and their uses and drawbacks. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Fletcher [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: Fax to: 06/09/2004 04:24 Subject: A Table with a timestamp as transaction date and primary key... PM I read some articles that the use of SQL's TIMESTAMP in a table is use for recording the actual date/time that the row was inserted and for row(s) that is/are updated. I'm a little troubled by that because I want a table with a timestamp in the first column to be the transaction date which can be done by insert and that the timestamp not be altered with the SQL update of any sort. I also want the TIMESTAMP to be a primary key. So, what is my options? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
