- Normalization
- Keys
- Relationships
- First Normal Form
- Second Normal Form
- Third Normal Form
- MySQL Data Types
- NULL and Default Values
- Indexes
- Final Design Steps
This chapter is from the book
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) |