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.

What is your question? You set your field to not null and you set it to NULL. It will default to 0 as is expected. What are they doing in windows according to you? Setting the field to NULL? That would be weird. — Nanne
– Nanne, Commented May 22, 2012 at 13:19
@Nanne and @ Quassnoi In windows it doesn't allow null values to update and shows original salaries, but here it is showing all zeros. Yes, it may be weird but for me results were unexpected. :) — kik
– kik, Commented May 22, 2012 at 13:30
What did you use to perform the query, just a commandline mysql client? What do you mean "it doens't allow"? You're getting an error instead of a warning? — Nanne
– Nanne, Commented May 22, 2012 at 13:33
can you edit your question and show the output of this command: show create table employee; — jcho360
– jcho360, Commented May 22, 2012 at 13:34

Marcus Adams · Accepted Answer · 2012-05-22 13:51:53Z

12

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.

edited May 22, 2012 at 13:51

Marcus Adams

54k9 gold badges98 silver badges146 bronze badges

answered May 22, 2012 at 13:40

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kik Over a year ago

+1 That was awesome.:) but where did i get my.cnf file in ubuntu?

Quassnoi Over a year ago

/etc/mysql/my.cnf I believe but you better check.

kik Over a year ago

That was correct path but it is saying "could not save file under /etc/mysql/my.cnf. Save under a Different name? "

Quassnoi Over a year ago

@kik: it's not world-editable, as you can figure. Run the editor under sudo.

Dunhamzzz · Accepted Answer · 2012-05-22 13:36:21Z

4

I don't see the problem, you set the column to NOT NULL, (which doesn't allow NULL values) and now it won't let you set it to NULL, which would be the expected behaviour.

The reason you have 0s in your DB is because 0 would be the result of casting NULL to an int.

edited May 22, 2012 at 13:36

answered May 22, 2012 at 13:18

Dunhamzzz

14.8k4 gold badges54 silver badges74 bronze badges

3 Comments

Quassnoi Over a year ago

Result of casting NULL to an INT is a NULL.

kik Over a year ago

+1 for clarified almost. But why it is not performing same in windows? why it is giving Zeros in UBUNTU? :)

Dunhamzzz Over a year ago

I think Quassnoi has nailed it :)

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related