DEFAULT and INSERT
If you attempt to insert a row expression of the form DEFAULT VALUES, an error is returned if any column in the table is not defined with a DEFAULT value.
If you attempt to insert an explicit column_list in which a value is omitted, an error is returned if no DEFAULT is defined for the omitted column and if the column was declared NOT NULL.
DEFAULT and Period Data Types
- DEFAULT NULL
- DEFAULT value
The specified value must be either a Period literal or a Period value constructor.When a Period value constructor is used for specifying the default value, the following rules apply:
- A Period value constructor with single argument must not be used as a default value; otherwise, an error is reported.
- The beginning bound must be specified using a DateTime literal, DATE, CURRENT_DATE, or CURRENT_TIMESTAMP[(n)]; otherwise, an error is reported.
- If the beginning bound is a DateTime literal, the ending bound must be specified using a DateTime literal or, if the beginning bound has a DATE or TIMESTAMP data type, UNTIL_CHANGED. Otherwise, an error is reported.
- If the beginning bound is DATE, CURRENT_DATE, or CURRENT_TIMESTAMP[(n)], the ending bound must be UNTIL_CHANGED or a DateTime literal specifying the equivalent value of UNTIL_CHANGED; otherwise, an error is reported.
- DEFAULT USER
- DEFAULT DATE
- DEFAULT TIME
- DEFAULT CURRENT_DATE
- DEFAULT CURRENT_TIME[(n)]
- DEFAULT CURRENT_TIMESTAMP[(n)]
Column Data Types and DEFAULT Values
A default value must be compatible with the data type specified for the column it is inserted into. For example, the phrase INTEGER DEFAULT 3.5 is not valid and returns an error.
For DateTime data types, SQL Engine performs an implicit conversion if the default value specified in a CREATE/ALTER TABLE statement differs from the data type of the column. For example, the following statement is valid:
CREATE TABLE tab1 (F1 INT, F2 TIMESTAMP(0) DEFAULT CURRENT_DATE);
The following table lists the default values you can specify for each of the column types.
|Column Data Type||Supported Default Values|
- CREATE TABLE tab2 (i INT, j INTERVAL DAY DEFAULT 4);
- CREATE TABLE tab3 (i INT, j INTERVAL HOUR TO MINUTE DEFAULT '11:23' );
For more information about implicit conversions of DateTime data types, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
The default value (excepting keywords) for character columns must be in the repertoire of the character data type defined for the column and session character set.
In an ALTER TABLE statement, the DEFAULT phrase can be used with a keyword to override a previously-defined value.
Unsupported Data Types
- BLOB or CLOB
- UDT, with the exception of DEFAULT NULL
DEFAULT Values and CHECK Constraints
A default value must not violate any CHECK constraints specified for the column. If a default value would violate a CHECK constraint, the conflict is not recognized at the time the table is defined or altered. The conflict is recognized the first time that an INSERT or UPDATE attempts to enter a NULL, which would be replaced by the default.
For character data, constraints are checked using the current session collation. Therefore, it is possible for a default value to meet the constraint for one collation and violate the constraint for another collation.
DEFAULT Values and Identity Columns
You cannot specify the DEFAULT attribute for Identity columns.
DEFAULT Values and Built-In Functions
A keyword used as a constant_value inserts a string that is already known to the system, such as the system date, the system time, or the name of the user defining the column.
Use of the Teradata SQL built-in functions DATE and TIME as default values is deprecated. They are non-ANSI standard and continue in the language only to maintain backward compatibility. Use the ANSI-standard CURRENT_DATE and CURRENT_TIME instead.
Maximum Length for a DEFAULT Value
The maximum length of constant_value is 510 characters for character columns, and 510 bytes for byte columns.
System Values for DEFAULT Phrase
Unlike the Teradata WITH DEFAULT phrase, there are no system default values for the DEFAULT phrase.
Values for DEFAULT Phrase Keywords
The following table lists the DEFAULT phrase forms, the function performed by each variable or keyword, and the corresponding default data types.
|This form …||Inserts …||Using this data type …|
|DEFAULT constant_value||the value defined as the default for the column||the type defined for the table column.|
|DEFAULT DATE quotestring||the date value specified by quotestring as the default for the column||DATE.|
|DEFAULT NULL||a NULL||None.|
|DEFAULT CURRENT_DATE||the current system date||DATE.|
|DEFAULT CURRENT_TIME||the current system time||TIME.|
|DEFAULT CURRENT_TIMESTAMP||the current system date and time||TIMESTAMP.|
Teradata extension to the ANSI standard
|the user name of the current user||CHAR(n) CHARACTER SET UNICODE
VARCHAR(n) CHARACTER SET UNICODE
where n is the length of the longest permissible user name.
If DEFAULT NULL is specified with the NOT NULL phrase in CREATE TABLE or ALTER TABLE ADD statements, no error or warning messages are returned for the statements. However, an error occurs the first time an INSERT or UPDATE attempts to enter a null.
You can assign a literal value, such as a blank, as the DEFAULT for a column as follows:
CHAR(1) DEFAULT ' ' ...