Why does MySQL allow to update a NOT NULL column to NULL?

Question

I'm running `MySql` in `ubuntu 10.10`. I created a table called `'employee'` having 3 field names empno, name and salary. Inserted few entities. In the middle of the process i want to change salary attribute as `'NOT NULL'`. I Alter the table as ``` ALTER TABLE employee MODIFY salary int(10) NOT NULL; ``` Query executed. I wanted to test by using command, ``` UPDATE employee SET salary=NULL; Query OK, 15 rows affected, 15 warnings (0.06 sec) Rows matched: 15 Changed: 15 Warnings: 15 ``` also gave warnings `" (Code 1048): Column 'salary' cannot be null "`(Repeated for every row) But when i saw my table , All salaries were Zeros(`'0'`). Same queries result in error instead of warning in WINDOWS XP's MySql I checked in both INNODB and MYISAM engines but same Result. Please help me to know what happened beside processing.

Accepted Answer

You must not have `SQL_MODE` set to strict on you ubuntu installation.

Issue

```
SET SQL_MODE='STRICT_ALL_TABLES'
```

or add

```
SQL_MODE='STRICT_ALL_TABLES'
```

under `[mysqld]` to your `my.cnf` on `Ubuntu`.

Why does MySQL allow to update a NOT NULL column to NULL?

Solution

Problem