Using a Built-in Function as a Default Value

You can specify a built-in function as the default column value. The following table lists built-in functions that you can specify, the data type requirements, and the recommended size (in bytes) for their corresponding columns.

These column sizes are recommended because, if the column length is too small to store the default value during INSERT or ALTER TABLE operations, the database server returns an error.

Built-In Function	Data Type Requirement	Recommended Size
CURRENT	DATETIME column with matching qualifier	Enough bytes to store the longest DATETIME value for the locale
DBSERVERNAME, SITENAME	CHAR, VARCHAR, NCHAR, NVARCHAR, or CHARACTER VARYING column	128 bytes
TODAY	DATE column	Enough bytes to store the longest DATE value for the locale
USER	CHAR, VARCHAR, NCHAR, NVARCHAR, or CHARACTER VARYING column	32 bytes

In Dynamic Server, you cannot designate a built-in function (that is, CURRENT, USER, TODAY, SITENAME, or DBSERVERNAME) as the default value for a column that holds an opaque or distinct data type. In addition, larger column sizes are required if the data values are encrypted, or if they are encoded in the Unicode character set of the UTF-8 locale. (See the description of the SET ENCRYPTION statement later in this chapter for more information about the storage size requirements for encrypted data.)

The following example creates a table called accounts. In accounts, the acc_num,acc_type, and acc_descr columns have literal default values. The acc_id column defaults to the login name of the user.

Single-Column Constraint Format

Use the Single-Column Constraint format to associate one or more constraints with a column, in order to perform any of the following tasks:

Create one or more data-integrity constraints for a column.
Specify a meaningful name for a constraint.

Specify the constraint-mode that controls the behavior of a constraint during insert, delete, and update operations.

Single-Column Constraint Format:
 
|--+-----------------------------------------------------+------>
   '-NOT NULL--+---------------------------------------+-'
               |  (1)                             (2)  |
               '--------| Constraint Definition |------'
 
   .-------------------------------------------------------------------------------.
   V      (1)                                                                      |
>----+-+--------DISTINCT-+------------+--+---------------------------------------+-+--|
     | +-UNIQUE----------+            |  |  (1)                             (2)  |
     | '-PRIMARY KEY-----'            |  '--------| Constraint Definition |------'
     |                         (3)    |
     '-+-| REFERENCES Clause |------+-'
       |                  (4)       |
       '-| CHECK Clause |-----------'

Notes:

Informix extension
See page Constraint Definition
See page REFERENCES Clause
See page CHECK Clause

The following example creates a standard table with two constraints: num, a primary-key constraint on the acc_num column; and code, a unique constraint on the acc_code column:

CREATE TABLE accounts (
   acc_num   INTEGER PRIMARY KEY CONSTRAINT num,
   acc_code  INTEGER UNIQUE CONSTRAINT code,
   acc_descr CHAR(30))

The types of constraints used in this example are defined in sections that follow.