Viktor Viktor - 3 months ago 23
MySQL Question

Error code 1067, SQL state 42000: Invalid default value for 'memberSince' when compiling a table

I'm adding a new table to the list of existing tables:

CREATE TABLE Counselor (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
firstName VARCHAR (50),
nickName VARCHAR (50),
lastName VARCHAR (50),
telephone VARCHAR (25),
email VARCHAR (50),
memberSince DATE DEFAULT '0000-00-00',
PRIMARY KEY (id)
);


Execution is interrupted with the following message:

Error code 1067, SQL state 42000: Invalid default value for 'memberSince'


What can I do to fix it?

Answer

In MySQL, '0000-00-00' is not a valid date. According to the MySQL docs:

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

I recommend using NULL if you don't have a meaningful default value you can use.

CREATE TABLE Counselor (
...
memberSince DATE DEFAULT NULL,
...
Comments