ERROR 1364 (HY000): Field 'MY_DATE' doesn't have a default value

Question

create table MYTABLE
{
    MY_DATE int NOT NULL AUTO_INCREMENT,
    NAME varchar(255) NOT NULL UNIQUE
};
INSERT INTO MYTABLE(NAME)values(jessica);

Why do I get this error?

ERROR 1364 (HY000): Field 'MY_DATE' doesn't have a default value

Answer 1

From the docs:

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value.

I think you'll find that, because you're not indexing MY_DATE, it may be silently ignoring the AUTO_INCREMENT option (I can't guarantee that, it's just supposition, but the note in the documentation is still relevant).

All the samples I can see on the AUTO_INCREMENT stuff have (the equivalent of):

PRIMARY KEY (MY_DATE)

Alternatively, you may be running in strict SQL mode. All the docs I've seen seem to indicate that, in the absence of a specific default, unlisted columns in an insert will get NULL if they're nullable, or the type default if the not nullable:

If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 10.1.4, “Data Type Default Values”.

For an integral type, that default is 0 (which should kick in the auto increment anyway).

However, in strict SQL mode (from here):

an error occurs for transactional tables and the statement is rolled back.