Specifying Domain Defaults

From InterBase
Jump to: navigation, search

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, a NULL 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 which USER 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

Advance To: