Publishers of technology books, eBooks, and videos for creative people

Home > Articles > Web Design & Development

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

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)


  • + Share This
  • 🔖 Save To Your Account