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.