CODEWITHSUNDEEP
phpmysqlvalidationdefault
Frank van Wensveen
Frank van Wensveen

Reputation: 562

In MySQL tables is it important to assign a default value to a column and why?

When I update rows in a MySQL table I get a warning that "Field xyz doesn't have a default value". Which is true; it doesn't. I validate all data obtained from MySQL in my PHP code.

The fact that MySQL warns me makes me wonder: is it important to have default values assigned to all columns and if so, why? Performance? Predictable behavior? Simplification of the calling code? Other Best Practices or issues that I'm not aware of?

Upvotes: 0

Views: 291

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Apparently, you are declaring your columns to be NOT NULL. This is allowed and reasonable. However, when you insert a new row into a table, MySQL needs to know what the value is. Here are three ways you can put in a value:

  • In the insert statement, you can explicitly list a value.
  • In the create table statement, you can have a default value. NULL is the default if none is listed.
  • The table can have an insert trigger that specifies values for columns.

Because MySQL will use NULL as the default, you would be getting the error only when NULL is not allowed in the column.

EDIT:

The original answer is not 100% complete, because MySQL will make up values when used in its normal mode. These are described here in the documentation. So, the correct fourth bullet is:

  • MySQL will insert default values of 0 for numerics and '' for strings under most, but not all, circumstances

Because the circumstances are pretty broad, it is likely that you are actually operating in strict mode or have this default behavior turned off.

Upvotes: 1

Related Questions