The following example set uses this table definition:
CREATE TABLE table7 ( col1 INTEGER, col2 INTEGER DEFAULT 10, col3 INTEGER DEFAULT 20, col4 CHARACTER(60) );
The following INSERT request is valid:
INSERT INTO table7 VALUES (1, 2, DEFAULT, 'aaa');
The DEFAULT function evaluates to the default value of col3, the third column position in the insert list. The example INSERT request inserts the value 20 into table7 for col3.
The resulting row is as follows:
col1 col2 col3 col4 ---------- ----------- ----------- ----------- 1 2 20 aaa
The following INSERT request is valid:
INSERT INTO table7 (col1, col3, col4) VALUES (1, DEFAULT, 'bbb');
The DEFAULT function evaluates to the default value of col3 because DEFAULT is specified second in the expression list, and the second column in the column list is col3.
The resulting row is as follows:
col1 col2 col3 col4 ---------- ----------- ----------- ---------- 1 10 20 bbb
You can specify a DEFAULT function with a column name in an expression list. This is a Teradata extension to the ANSI SQL:2011 standard.
The following INSERT request is valid:
INSERT INTO table7 VALUES (1, 2, DEFAULT(col2), 'aaa');
The DEFAULT function evaluates to the default value of col2 because col2 is passed as an input argument to the DEFAULT function. The INSERT results in the value 10 for col3 in the row because col3 is the third column position in the insert list.
The resulting row is as follows:
col1 col2 col3 col4 ----------- ----------- ----------- ------------ 1 2 10 aaa
The following INSERT request is valid:
INSERT INTO table7 (col1, col3, col4) VALUES (1, DEFAULT(col2), 'bbb');
Because col2 is passed as an input argument to the DEFAULT function, the function evaluates to the default value of col2. Because the second column in the column list is col3, the system assigns it the value 10, which is the default value of col2).
The resulting row is as follows:
col1 col2 col3 col4 ----------- ----------- ----------- ------ 1 10 10 bbb
You can specify the DEFAULT function with a column name anywhere in the expression. This is a Teradata extension to the ANSI SQL:2011 standard.
The following INSERT request is valid:
INSERT INTO table7 VALUES (1, 2, DEFAULT (col2)+5, 'aaa');
The DEFAULT function evaluates to the default value of col2 plus 5 (or 10+5). Because col3 is in the third column position in the insert list, the resulting row is as follows:
col1 col2 col3 col4 ---------- ----------- ----------- ----------------- 1 2 15 aaa
The following INSERT request is valid:
INSERT INTO table7 (col1, col3, col4) VALUES (1, DEFAULT(col2)+5, 'bbb');
The DEFAULT function evaluates to the default value of col2 plus 5 (or 10+5). Because the second column in the column list is col3, the system assigns the value 15 to it. The resulting row is as follows:
col1 col2 col3 col4 ---------- ----------- ----------- --------------- 1 10 15 bbb
When there is no explicit default value associated with a column, the DEFAULT function evaluates to null.
Assume the following table definition for this example:
CREATE TABLE table9 ( col1 INTEGER, col2 INTEGER NOT NULL, col3 INTEGER NOT NULL DEFAULT NULL col4 INTEGER CHECK (Col4>100) DEFAULT 99 );
The following INSERT request is valid:
INSERT INTO table9 VALUES (DEFAULT, 99, 101, 101);
In this example, col1 is nullable and does not have an explicit default value associated with it. Therefore, the DEFAULT function evaluates to null.
The resulting row is as follows:
col1 col2 col3 col4 ----------- ----------- ----------- ------------ ? 99 101 101
Assume the following table definition for the following examples:
CREATE TABLE table10 col1 INTEGER , col2 INTEGER DEFAULT 55, col3 INTEGER NOT NULL DEFAULT 99 );
The following examples are correct and use Teradata extensions to the DEFAULT function:
INSERT INTO table10 VALUES (1, DEFAULT(col2), DEFAULT(col3)); INSERT INTO table10 (col1, col2) VALUES (1, DEFAULT(col2)); INSERT INTO table10 (col1, col2, col3) VALUES (1, DEFAULT(col2), DEFAULT(col3));
The following examples are correct and use ANSI SQL:2011-compliant syntax for the DEFAULT Function:
INSERT INTO table10 VALUES (1, DEFAULT, DEFAULT); INSERT INTO table10 (col1, col2) VALUES (1, DEFAULT); INSERT INTO table10 (col1, col2, col3) VALUES (1, DEFAULT, DEFAULT);
The resulting row for all previous insert operations is the following:
col1 col2 col3 ----- ----------- ----------- 1 55 99
The following INSERT requests are all equivalent to one another. The first example uses an ANSI-SQL:2011-compliant syntax:
INSERT INTO table10 VALUES (5, DEFAULT, 99);
The following example uses Teradata extensions to the ANSI-SQL:2011 syntax:
INSERT INTO table10 VALUES (5, DEFAULT(col2), 99);
The resulting row for both of these insert operations is as follows:
col1 col2 col3 ------ ----------- ----------- 5 55 99