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 14, “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.
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 |
