List:Internals« Previous MessageNext Message »
From:Arnold Daniels Date:July 29 2007 10:16pm
Subject:Re: multiple current_timestamp
View as plain text  
Hi,

I see that I haven't made myself clear. Please take the statement to 
create a table below as example.

    CREATE TABLE `mytable` (
    `id` INT UNSIGNED NOT NULL ,
    `description` VARCHAR( 255 ) NOT NULL ,
    `date_insert` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    `date_update` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT
    CURRENT_TIMESTAMP ,
    PRIMARY KEY ( `id` )
    ) ENGINE = MYISAM;


This triggers the error:

    #1293 - Incorrect table definition; there can be only one TIMESTAMP
    column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Let's say this table does not trigger an error and I would do the 
following queries:

    INSERT INTO `mytable` (`description`) VALUES ("My first item");
    UPDATE `mytable` SET `description` = "Changed my first item" WHERE
    `id`=1;

The fields would not hold the same data.

At this moment you cannot use 'ON UPDATE CURRENT_TIMESTAMP' if you also 
have a field like `date_insert`. Instead you need to create a trigger to 
do this behavior.

I hope I managed to make my issue clear with this example.

Best regards,
Arnold


Eric Bergen wrote:
> It makes sense to only allow one field to have current_timestamp
> because having more than one field with current_timestamp would mean
> both columns are storing the same data. Is it really that difficult to
> have date_updated be current_timestamp and date_added set to null in
> the insert query? I don't understand the need for triggers. What
> problem are you trying to solve with code or triggers?
>
>
>
> On 7/28/07, Arnold Daniels <info@stripped> wrote:
>   
>> Hi all,
>>
>> Why can there be only one timestamp field with current_timestamp in
>> default or on update?
>>
>> I've been wondered about this for a long time. A table often has a field
>> `date_added`, with the current timestamp only as default, and a field
>> `date_changed` with a changing timestamp upon updating. Basically it is
>> irritating me, since now I have to either solve this in code or use a
>> trigger. Also because I do not understand why this limitation is necessary.
>>
>> Is this something that is subjected to change in MySQL 6? I haven't seen
>> any plans for it.
>>
>> Best regards,
>> Arnold
>>
>> --
>> MySQL Internals Mailing List
>> For list archives: http://lists.mysql.com/internals
>> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
>>
>>
>>     
>
>
>   
Thread
multiple current_timestampArnold Daniels29 Jul
  • Re: multiple current_timestampEric Bergen29 Jul
    • Re: multiple current_timestampArnold Daniels30 Jul
      • Re: multiple current_timestampEric Bergen30 Jul
        • Re: multiple current_timestampJim Winstead30 Jul
          • Re: multiple current_timestampTobias Asplund30 Jul
  • Re: multiple current_timestampDmitri Lenev30 Jul
  • mmaping and index file(.MYI) in mysql 5.1.22-rc?ajay singh20 Dec