Mysql NULL and Default Values

NULL and Default Values

As you have already seen, there are a few attributes you can assign when defining your data types, including UNSIGNED and ZEROFILL. Two more options are to dictate whether or not the value of a column can be NULL and to set a default value.

The NULL value, in databases and programming, is the equivalent of saying that the field has no value (or it is unknown). Ideally, every record in a database should have value, but that is rarely the case in practicality. To enforce this limitation on a field, you add the NOT NULL description to its column type. For example, a primary key might now be described as client_id SMALLINT(3) UNSIGNED NOT NULL and Default Values NULL

When creating a table you can also specify a default value. In cases where a large portion of the records will have the same contents, presetting a default will save you from having to specify a value when inserting new rows, unless that value is different from the norm. One example might be gender ENUM('M', 'F') DEFAULT 'F'

Table 3.4 incorporates these two new ideas.

Table 3.4 I've added NOT NULL descriptions and DEFAULT values for a few of my columns to further improve the database design.

Accounting Database
Column Name	Table	Column Type
Invoice Number	Invoices	SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0
Client ID	Invoices	SMALLINT(3) UNSIGNED
Invoice Date	Invoices	DATE NOT NULL
Invoice Amount	Invoices	DECIMAL(10,2) UNSIGNED NOT NULL
Invoice Description	Invoices	TINYTEXT
Client ID	Clients	SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0
Client Name	Clients	VARCHAR(40) NOT NULL
Client Street Address	Clients	VARCHAR(80)
Client City	Clients	VARCHAR(30)
Client State	Clients	CHAR(2)
Client Zip	Clients	MEDIUMINT(5) UNSIGNED
Client Phone	Clients	VARCHAR(14)
Contact Name	Clients	VARCHAR(40)
Contact Email Address	Clients	VARCHAR(60)
Expense ID	Expenses	SMALLINT(4) UNSIGNED NOT NULL DEFAULT 0
Expense Category ID	Expenses	TINYINT(3) UNSIGNED
Expense Amount	Expenses	DECIMAL(10,2) UNSIGNED NOT NULL
Expense Description	Expenses	TINYTEXT
Expense Date	Expenses	DATE
Expense Category ID	Expense Categories	TINYINT(3) UNSIGNED
Expense Category	Expense Categories	VARCHAR(30)

Tips

• Primary keys cannot contain NULL values, in accordance with proper database design and with how MySQL functions.
• If an ENUM column is set as NOT NULL, the default value will automatically be the first of the possible allowed values.
• Just to clarify what NULL is, understand that NULL has a different value than the number zero, an empty string (""), or a space (" ").