Databases
StackOverflow2013
Postsdbo
POMysql does not error on insert or update to null value on not null field
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMysql does not error on insert or update to null value on not null field
    text
    copied!<p><br/> I have a table</p> <pre><code>CREATE TABLE `devicelist` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ip` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, `serial` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `networkname` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `login` varchar(130) COLLATE utf8_unicode_ci DEFAULT NULL, `password` varchar(130) COLLATE utf8_unicode_ci DEFAULT NULL, `peopleid` int(10) unsigned NOT NULL, `deviceid` int(10) unsigned NOT NULL, `placeid` int(10) unsigned NOT NULL, `stationid` int(10) unsigned NOT NULL, `place` text COLLATE utf8_unicode_ci, `date` date NOT NULL, `active` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `placeid` (`placeid`), KEY `stationid` (`stationid`), KEY `peopleid` (`peopleid`), KEY `deviceid` (`deviceid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; </code></pre> <p>as you see the <strong>serial</strong> is set to <strong>NOT NULL</strong>,<br/> but when i run query like </p> <pre><code>UPDATE DeviceList SET serial = NULL WHERE id = 1 </code></pre> <p>It outputs </p> <pre><code>1 row affected. ( Query took 0.0079 sec ) </code></pre> <p>Why is this inserting null to not null field?<br/> I want an error to occure:D </p> <p>///****************************** EDIT</p> <p>well i found the answer<br/> the problem was in sql_mode<br/> <a href="https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict" rel="nofollow">https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict</a><br/> my sql_mode was empty by default<br/> iw changed it to STRICT_ALL_TABLES via<br/></p> <pre><code>SET GLOBAL sql_mode='STRICT_ALL_TABLES' </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload