MySQL allows inserts even when required fields are not present

Question

I've got a database table for users that has a setup similar to this:

+---------------------+--------------+------+-----+------------+----------------+
| Field               | Type         | Null | Key | Default    | Extra          |
+---------------------+--------------+------+-----+------------+----------------+
| id_user             | int(5)       | NO   | PRI | NULL       | auto_increment |
| login               | varchar(50)  | NO   |     |            |                |
| password            | varchar(50)  | NO   |     |            |                |
| address             | varchar(255) | NO   |     | NULL       |                |
+---------------------+--------------+------+-----+------------+----------------+

Since the address field is set to not null and the default is null, this should effectively make the address field required, right?

But when I perform a query such as the following:

INSERT INTO mydatabase.users
SET
    mydatabase.users.login='test_username',
    mydatabase.users.password='test_password'

It successfully saves the user without complaining about the address field, which is set to an empty space.

What could be causing this? I expect an error in this type of situation. I've tried it with MySQL 5.5 and 5.6.

Answer 1

Strict SQL mode is not enabled in the configuration file, therefore mysql relaxes certain controls over data insertion:

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.