Why am I getting the \"Invalid default value for \'create_date\'\" error when creating a table with a timestamp field?

Invalid Default Value for Timestamp Field

The error "Invalid default value for 'create_date'" occurs when attempting to create a table with a timestamp field that has a default value of '0000-00-00 00:00:00'. This error is triggered by the SQL Mode - NO_ZERO_DATE.

The NO_ZERO_DATE mode prevents the insertion of '0000-00-00' as a valid date. When in strict mode, this value is prohibited, even with the IGNORE option. In non-strict mode, the date may be accepted but a warning is generated.

To resolve this error, either disable the NO_ZERO_DATE mode or use a different default value for the timestamp field.

<code class="sql">SET SQL_MODE = 'ALLOW_INVALID_DATES';</code>

Alternatively, specify a valid default value for the timestamp field:

<code class="sql">CREATE TABLE IF NOT EXISTS `erp`.`je_menus` (
    `id` INT(11) NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(100) NOT NULL ,
    `description` VARCHAR(255) NOT NULL ,
    `live_start_date` DATETIME NULL DEFAULT NULL ,
    `live_end_date` DATETIME NULL DEFAULT NULL ,
    `notes` VARCHAR(255) NULL ,
    `create_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    `created_by` INT(11) NOT NULL ,
    `update_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP  ,
    `updated_by` INT(11) NOT NULL , 
    `status` VARCHAR(45) NOT NULL ,
    PRIMARY KEY (`id`) ) 
    ENGINE = InnoDB;</code>

The above is the detailed content of Why am I getting the \"Invalid default value for \'create_date\'\" error when creating a table with a timestamp field?. For more information, please follow other related articles on the PHP Chinese website!