Google+ Followers

zondag 8 december 2013

NOT NULL and DEFAULT

For some reason lots of people define database columns as something like NOT NULL DEFAULT 0.
The idea being that the column should not accept any NULL values, and new records should start with the value of 0.

And indeed, this is exactly how it works in PostgreSQL:

drop table if exists foo;
create table foo (id serial, i int not null default 0);

insert into foo (id, i) values (1, null);

Gives an error for the not null violation.

insert into foo (id) values (1);

Column i gets a valiue of 0.

update foo set i=null;

Gives again an error for the not null violation.


MySQL however...


create table foo (id integer auto_increment primary key, i integer not null default 0);
insert into foo (id,i) values (1,null);
ERROR 1048 (23000): Column 'i' cannot be null

INSERT INTO foo (id) VALUES (1);

Column i is set to 0.

So far so good, but what happens when you insert a NULL through an UPDATE?

UPDATE foo SET i =NULL;
Rows matched: 1  Changed: 0  Warnings: 1

No error, one warning, and i is equal to zero. But what happend? Well MySQL just reset the column back to the default value.

UPDATE foo SET i = 2;

Column i is set to 2, as expected.

UPDATE foo SET i = NULL;
Rows matched: 1  Changed: 0  Warnings: 1

No error, one warning, and i is back to zero. So, if you try to put NULL into the field using an INSERT it will fail on the NOT NULL, but if you try the same using an UPDATE you only get a warning about the NOT NULL and the DEFAULT value is used.

Think about this: your NOT NULL only works sometimes and your data is actuallt changed to the default if you try to remove the current value.