How to Manage Multiple TIMESTAMP Columns with CURRENT_TIMESTAMP in MySQL?

Managing Multiple TIMESTAMP Columns with CURRENT_TIMESTAMP

In MySQL, defining multiple TIMESTAMP columns with CURRENT_TIMESTAMP default values can lead to the error "Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause." To overcome this limitation, consider the following approach in recent MySQL editions (e.g., 5.6.25):

Solution:

Modify the table definition as follows:

<code class="sql">CREATE TABLE `msgs` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `msg` VARCHAR(256),
    `ts_create` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ts_update` TIMESTAMP NOT NULL,
    CONSTRAINT UPDATE_ON_CHANGE_ts_update
        FOREIGN KEY (`ts_update`) REFERENCES `msgs` (`ts_create`)
        ON DELETE SET NULL
        ON UPDATE CURRENT_TIMESTAMP
)</code>

In this modified schema:

• ts_create remains as a regular TIMESTAMP column with a CURRENT_TIMESTAMP default.
• ts_update is defined as a TIMESTAMP column with NOT NULL constraint.
• A foreign key constraint is created between ts_update and ts_create, which allows for cascading updates from ts_create to ts_update.
• The ON UPDATE CURRENT_TIMESTAMP action is applied to ts_update via the foreign key constraint, ensuring that the ts_update column is updated to the current timestamp whenever ts_create is modified.

This approach allows you to maintain the desired behavior of keeping track of both record creation and update timestamps while avoiding the MySQL error.

The above is the detailed content of How to Manage Multiple TIMESTAMP Columns with CURRENT_TIMESTAMP in MySQL?. For more information, please follow other related articles on the PHP Chinese website!