Specifying Domain Defaults
Go Up to Creating Domains (Data Definition Guide)
You can set an optional default value that is automatically entered into a column if you do not specify an explicit value. Defaults set at the column level with CREATE TABLE
or ALTER TABLE
override defaults set at the domain level. Defaults can save data entry time and prevent data entry errors. For example, a possible default for a DATE
column could be today’s date, or in a (Y/N) flag column for saving changes, “Y” could be the default.
Default values can be:
- literal: The default value is a user-specified string, numeric value, or date value.
NULL
: If the user does not enter a value, aNULL
value is entered into the column.USER
: The default is the name of the current user. If your operating system supports the use of 8- or 16-bit characters in user names, then the column into whichUSER
will be stored must be defined using a compatible character set.
In the following example, the first statement creates a domain with USER
named as the default. The next statement creates a table that includes a column, ENTERED_BY
, based on the USERNAME
domain.
CREATE DOMAIN USERNAME AS VARCHAR(20) DEFAULT USER; CREATE TABLE ORDERS (ORDER_DATE DATE, ENTERED_BY USERNAME, ORDER_AMT DECIMAL(8,2)); INSERT INTO ORDERS (ORDER_DATE, ORDER_AMT) VALUES ('1-MAY-93', 512.36);
The INSERT
statement does not include a value for the ENTERED_BY
column, so InterBase automatically inserts the user name of the current user, JSMITH
:
SELECT * FROM ORDERS; 1-MAY-93 JSMITH 512.36