DEFAULT Clause
The WITH|NOT DEFAULT clause in the column specification specifies whether a column requires an entry.
This clause has the following format:
[WITH] DEFAULT default_spec | WITH DEFAULT | NOT DEFAULT
[WITH] DEFAULT default_spec
Indicates that if no value is provided (because none is required), the DBMS Server inserts the default value. The default value must be compatible with the data type of the column.
For character columns, valid default values include the constants: USER, CURRENT_USER, and SYSTEM_USER.
For boolean columns, valid default values include FALSE or TRUE.
WITH DEFAULT
Indicates that if no value is provided, the DBMS Server inserts 0 for numeric and money columns, an empty string for character columns, the current date for ANSI date columns, and the current timestamp for timestamp columns.
NOT DEFAULT
Indicates the column is mandatory (requires an entry).
The following is an example of using the DEFAULT clause:
CREATE TABLE DEPT(dname CHAR(10),
location CHAR(10) DEFAULT 'NY',
creation DATE DEFAULT '01/01/12',
budget MONEY DEFAULT 10000);