Chapter 8: Creating Databases and Tables

Defaults and null values

You can use defaults, that is, values that are supplied automatically when no entry is made, with both NULL and NOT NULL columns. A default counts as an entry. However, you cannot designate a NULL default for a NOT NULL column. You can specify null values as defaults using the default constraint of create table or using create default. The default constraint is described later in this chapter; create default is described in Chapter 13, “Defining Defaults and Rules for Data.”

If you specify NOT NULL when you create a column and do not create a default for it, an error message occurs when a user fails to make an entry in that column during an insert. In addition, the user cannot insert or update such a column with NULL as a value.

Table 8-1 illustrates the interaction between a column’s default and its null type when a user specifies no column value or explicitly enters a NULL value. The three possible results are a null value for the column, the default value for the column, or an error message.

**Table 8-1: Column definition and null defaults**
Column definition	User entry	Result
Null and default defined	Enters no value Enters NULL value	Default used NULL used
Null defined, no default defined	Enters no value Enters NULL value	NULL used NULL used
Not null, default defined	Enters no value Enters NULL value	Default used NULL used
Not null, no default defined	Enters no value Enters NULL value	Error Error

View this book as PDF