5

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?

  • 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. – Michael - sqlbot Nov 23 '16 at 22:30
6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.