Below is the query that I userd to execute to create a table:
CREATE TABLE `users_authentication` (
`id` int(11) NOT NULL,
`users_id` int(11) NOT NULL,
`token` varchar(255) NOT NULL,
`expired_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
But it shows an error message like this:
1067 - Invalid default value for 'expired_at'
Use TIMESTAMP instead of DATETIME.
In some versions of MySQL, it is not possible to use CURRENT_TIMESTAMP with DATETIME.
So, you can replace your script for this, for MySQL versions previous to 5.6.5:
CREATE TABLE `users_authentication` (
`id` int(11) NOT NULL,
`users_id` int(11) NOT NULL,
`token` varchar(255) NOT NULL,
`expired_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_at` TIMESTAMP NOT NULL,
`updated_at` TIMESTAMP NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Check here: How do you set a default value for a MySQL Datetime column?
The MySQL doc:
As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initialized and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table.
and for at most one TIMESTAMP column per table. You can add only one timestamp column with current date by default.CURRENT_TIMESTAMP is only acceptable on TIMESTAMP fields. DATETIME fields must be left either with a null default value, or no default value at all - default values must be a constant value, not the result of an expression.
CREATE TABLE users_authentication(
id int(30) NOT NULL,
user_id int(30) NOT NULL,
token varchar(40),
expired_at TIMESTAMP NOT NULL DEFAULT 0,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL
);
We cant use three TIMESTAMP like TIMESTAMP NOT NULL so we have to change this by setting different default value for all One Mysql Table with Multiple TIMESTAMP Columns
