Databases, mysql and the forbidden

Lets just recapitulate - relational databases are good at ensuring data consistency, right?

So lets try this:

CREATE TABLE user (
  id int NOT NULL AUTOINCREMENT,
  login NOT NULL,
  email NOT NULL
);

And then this

INSERT INTO user (login) VALUES ('mike');

What happens next?

You'd probably expect something along the lines of

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

Sorry, no. This is what I got:

Query OK, 1 row affected, 1 warnings (0.02 sec)

Thanks to this sucker:

SELECT @@sql_mode;

Which yields the "informative" empty string. What this means is that strict (other people would call this "normal") SQL mode is off. What happens when mysql is running in this "lax" mode is that columns with no default that aren't specified in INSERT statements and with a NOT NULL constraint are silently changed to empty strings.

Which lead me to the conclusion that some options should just be forbidden.

...and I'm sending my thoughts to the family of the developer who decided to disable strict sql mode. It must be a struggle. Every day.

Illustration by A Syn.