Mysql Not Null Columns accepting null values

Question

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.

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.

Károly Nagy · Accepted Answer · 2016-02-18 17:24:04Z

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
– ddutra, Commented Feb 18, 2016 at 17:48

Community · Accepted Answer · 2017-04-13 12:42:51Z

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

Stack Exchange Network

Mysql Not Null Columns accepting null values

2 Answers 2

Hot Network Questions

Mysql Not Null Columns accepting null values

2 Answers 2

Related

Hot Network Questions