Field both NOT NULL and DEFAULT NULL

Question

This MySQL table had me perplexed for a moment:

mysql> desc quux;
+---------------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+-----------------------+------+-----+---------+----------------+
| foobar | int(11) | NO | | NULL | |
(...)
mysql> show create table quux;
(...)
`foobar` int(11) NOT NULL,
(...)

Since the foobar field was never created with a DEFAULT clause, it gets automatically assigned DEFAULT NULL. However, at a first sight this looks like contradicting the fact that it was also defined as NOT NULL.

Then I realized that this schema aims to force to insert a value (and a non-NULL one) in foobar when adding a new record.

Is this an acceptable way to do so, or there are better ways?

• Michael - sqlbot: If you are caught up in the apparent contradiction, read it this way: "by default, this column has no value; however, a value is always required so it must be specified." There appears to be a logical inconsistency here, but there actually isn't, since NULL is not, itself, an actual value, but rather a marker signifying the absence of any value.

Answer 1

That's perfectly acceptable, it basically states:

You have to provide a value for this, but I have no clue what that would be.

If you define a default value, you claim that this is a sensible value for any row that doesn't supply a value - which is not really feasible for most attributes.

Think about a column named first_name - you want to make sure a value is supplied but there is no way you can come up with a sensible default value for that. Or a column named salary in an employee table.