In MySQL is it possible to have a NULL and a DEFAULT value inserted?

Question

I'm trying to set up a table row that does not need a value (no error message if empty) but will insert a default value if empty, as in:

ALTER TABLE tablename ALTER rowname TINYINT UNSIGNED NOT NULL;

ALTER TABLE tablename ALTER rowname SET DEFAULT 0;

I set it up this way, but the value inserted is still NULL.

Is this even possible?

Thanks!

Answer 1

The NOT NULL clearly says the value for this column cannot be NULL. So there is no NULL value used for the column. But it can hold the integer value 0 as it is a valid integer value.

Answer 2

It is a bit strange. Given this table definition:

create table def ( i int not null default 0 );

This statement works:

insert into def () values ();
select * from def;

+---+
| i |
+---+
| 0 |
+---+

But this statement generates an error:

insert into def values( null );
ERROR 1048 (23000): Column 'i' cannot be null

So it seems that "not null default 0" will supply the default of 0 when the column is not explicitly assigned a value (which would normally be assigned a NULL), but does not allow NULL to be explicitly assigned because of the NOT NULL attribute.

Answer 3

Are you sure that the value being inserted into the database is actually NULL, and that your programming language isn't just disregarding type and treating a 0 in the row as equivalent to NULL? If your column is defined as NOT NULL, MySQL shouldn't even be allowing NULL at all, let alone use it as the default.