DEFAULT

You may use the DEFAULT keyword in the following contexts:

The default value (literal or expression) that you specify in a CREATE TABLE or ALTER TABLE statement must meet the following criteria:

Also, in INSERT and UPDATE statements, you do not have to specify values for columns that have a DEFAULT value defined. In such a case, Pervasive.SQL computes the DEFAULT expression and uses the result value as the value of the column.

Syntax

See the syntax for the following statements:

Remarks

You can specify expressions as DEFAULT values for columns of all data types as shown in the following table.

Examples

The following statement creates a table named Tab1. The default value of the col1 column is the DATE part of timestamp 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 timestamp 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 SQL_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, Pervasive.SQL 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:

When you specify a scalar function as the default value for a column in INSERT and UPDATE statements, Pervasive.SQL invokes the specified scalar function to set the value of such a column to the result of the function call.

See Also

ALTER TABLE

CREATE TABLE

INSERT

UPDATE