I am having trouble with Mysql not null columns. It seems my mysql installation is accepting null values for NOT NULL columns.

My mysql version is 5.6.25-1~dotdeb+7.1(debian).

Take this table for instance:

CREATE TABLE `cities` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `state_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5562;

When I insert a value like this:

insert into cities(state_id) values (20);

Mysql spills a warning but commits the value anyway. Here is the warning.

12:51:43    insert into cities(state_id) values (20)    1 row(s) affected, 1 warning(s): 1364 Field 'name' doesn't have a default value 0.000 sec

If I remove the Unique Key unique_city_in_state I get the same behaviour.

I also tried creating the name column with a DEFAULT NULL, like so:

  `name` varchar(255) NOT NULL DEFAULT NULL,

This spills a error that would roughly translate to (Default value invalid for name).

I tried on a different mysql installation 5.1.73-1 (Debian) and I have the same behavior.

How can I have NULL values on the name column?

enter image description here

Now if I do this

insert into cities (name, state_id) values (null, 19);

I get the ERROR 1048 (23000) meaning the column name cannot be empty.

Any help is welcome.

up vote 6 down vote accepted

It actually doesn't accept NULL values it considers it as empty string. That's because you have your server in non-strict mode. That controls how MySQL handles invalid or missing values in inserts and updates. You can read more about modes here: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict

mysql> insert into cities(state_id) values (20);
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+

mysql> select name is null from cities where id = LAST_INSERT_ID();
+--------------+
| name is null |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into cities(state_id) values (20);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
  • Thank you very much. My PHP Framework also has a option to set strict to false or true on the connection itself :) Just found out about this thing. Thank you! – ddutra Feb 18 '16 at 17:48

To Károly Nagy's point,

You can use

SET SQL_MODE = 'STRICT_ALL_TABLES';

I'm sure you may already know but that is equivilent to:

SET @@SESSION.SQL_MODE = 'STRICT_ALL_TABLES';

Which, must be set for every session. And you can checky this by running this after the above statement.

SELECT @@SESSION.SQL_MODE; -- 'STRICT_ALL_TABLES'

SELECT @@GLOBAL.SQL_MODE; -- 'NO_ENGINE_SUBSTITUTION'

If you have access please set this at the global level:

SET @@GLOBAL.SQL_MODE = 'STRICT_ALL_TABLES';

Which becomes the default for every new session thereafter.

Cheers, Jay ;-]

NOTE: Tested On MySQL Version 5.6.23-log & MySQL Workbench 6.2.5

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.