You may use the DEFAULT keyword in the following contexts:
•Column definition of CREATE TABLE statement
•Column definition of ALTER TABLE statement
•VALUES clause of INSERT statement
•VALUES clause of UPDATE statement
The default value, either a literal or an expression, that you specify in a CREATE TABLE or ALTER TABLE statement must meet the following criteria:
•Be consistent with the data type of the column.
•Conform to any other constraint imposed on the column, such as range or length.
Also, in INSERT and UPDATE statements, you do not have to specify values for columns that have a DEFAULT value defined. In such cases, Zen computes the DEFAULT expression and writes the resulting value to the column.
You can specify expressions as DEFAULT values for columns of all data types in the following list:
•AUTOTIMESTAMP
•BFLOAT4, BFLOAT8
•BIGIDENTITY
•BIGINT, UBIGINT
•BINARY
•BIT
•CHAR
•CURRENCY
•DATE
•DATETIME
•DECIMAL
•DOUBLE
•IDENTITY
•INTEGER, UINTEGER
•LONGVARBINARY
•LONGVARCHAR
•NLONGVARCHAR
•NUMERIC
•NUMERICSA
•NUMERICSTS
•REAL
•SMALLIDENTITY
•SMALLINT, USMALLINT
•TIME
•TIMESTAMP
•TIMESTAMP2
•TINYINT, UTINYINT
•UNIQUEIDENTIFIER
•VARCHAR
In a CREATE TABLE statement or an ALTER TABLE statement, you may set a default value of zero (default 0 or default '0') for an IDENTITY, SMALLIDENTITY, or BIGIDENTITY data type. No other default value is permissible.
Examples
The following statement creates a table named Tab1. The default value of the col1 column is the DATE part of the time stamp returned by NOW().
CREATE TABLE Tab1
(col1 DATE DEFAULT NOW())
============
The following statement creates a table named Tab5. The default value of the col5 column is 200.
CREATE TABLE Tab5
(col5 INT DEFAULT 200)
============
The following statement creates a table named Tab8. The default value of the col8 column is the time stamp at the time of the INSERT or UPDATE.
CREATE TABLE Tab8
(col8 TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
============
The following statement creates a table named Tab6. The default value of the col6 column is the name of user, after executing an INSERT or UPDATE statement. Using a default user name is practical only when security is enabled. Otherwise, USER is always NULL.
CREATE TABLE Tab6
(col6 VARCHAR(20) DEFAULT USER)
============
The following statement shows an invalid example. The statement results in a parse-time error because TIME is not an allowed data type for a DATE column.
CREATE TABLE Tab
(col DATE DEFAULT CURTIME())
============
The following statement shows an invalid example. The statement results in a parse-time error. Although '3.1' is convertible to a number, it is not a valid integer.
CREATE TABLE Tab
(col SMALLINT DEFAULT '3.1')
============
The following statement shows an invalid example. The CREATE TABLE statement succeeds but the INSERT statement fails because -60000 is outside of the range supported by SMALLINT.
CREATE TABLE Tab
(col SMALLINT DEFAULT 3 * -20000)
INSERT INTO Tab values(DEFAULT)
============
The following statements show valid examples of setting a default value of zero for an IDENTITY and a SMALLIDENTITY data type.
CREAT TABLE t1 ( c1 IDENTITY DEFAULT '0' )
ALTER TABLE t1 ALTER c1 SMALLIDENTITY DEFAULT 0
============
The following statements show invalid examples of setting a default value for an IDENTITY and a SMALLIDENTITY data type.
CREATE TABLE t1 ( c1 IDENTITY DEFAULT 3 )
ALTER TABLE t1 ALTER c1 SMALLIDENTITY DEFAULT 1
Expressions as Default Values for Date or Time Columns
Defaults specify the values that are used in a column if you do not specify a value for the column when inserting a row.
To assign an expression as a default on a column, define a default value using the DEFAULT keyword in the CREATE TABLE statement. For more details on creating a table, see CREATE TABLE.
In addition to literals and NULL values, Zen also allows you to specify date and time scalar functions and expressions as default values for columns of the appropriate data types.
CURRENT_USER() is allowed as a default value for string columns CHAR and VARCHAR.
The following table lists the specific date and time scalar functions that you can invoke to provide the default value for a column of the appropriate data type.
Table 23 Date and Time Scalar Functions as Default Values
Function
Default for
CURRENT_DATE()
Date columns only
CURDATE()
Date columns only
CURRENT_TIME()
Time columns only
CURTIME()
Time columns only
CURRENT_TIMESTAMP()
Time stamp or date time columns
NOW()
Time stamp or date time columns
When you specify a scalar function as the default value for a column in INSERT and UPDATE statements, Zen invokes the specified scalar function to set the value of such a column to the result of the function call.