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.
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.
This table 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 |