Sybase IQ supports loading and inserting column default values using the following statements:
INSERT...VALUES
INSERT...SELECT
INSERT...LOCATION
LOAD TABLE
UPDATE
SELECT...FROM...FOR UPDATE
Sybase IQ handles defining and inserting column default values with the following requirements:
Sybase IQ permits you to specify DEFAULT values that cannot be evaluated by Sybase IQ. An error is reported when an INSERT, LOAD, or ALTER ADD operation is performed on a table that has an unsupported DEFAULT value.
Sybase IQ generates an error or warning when the server attempts to insert a DEFAULT value that is not compatible with the data type of the column. For example, if you define a default expression of ‘N/A’ to an integer column, then any insert or load that does not specify the column value generates an error or warning, depending on the setting of the CONVERSION_ERROR database option. See Table 7-8 for information on supported implicit data type conversions.
If a DEFAULT value is too long for a CHARACTER type column, Sybase IQeither truncates the string or generates an exception, depending on the setting of the STRING_RTRUNCATION database option.
If the DEFAULT value for a VARCHAR or LONG VARCHAR column is the zero-length string, Sybase IQ either inserts a NULL or zero-length string, depending on the setting of the NON_ANSI_NULL_VARCHAR database option.
If the DEFAULT value for a VARCHAR, CHAR, or LONG VARCHAR column is a string that contains a partial multi-byte character, then Sybase IQ may trim the partial multi-byte character before inserting the value, depending on the setting of the TRIM_PARTIAL_MBC database option.
Sybase IQ generates an error message every time the server attempts to insert the DEFAULT value of a column, if that default value violates the check constraint of either the table or the column.
If the LOAD TABLE DEFAULTS option is ON (the default) and the column has a default value, that value is used. If the DEFAULTS option is OFF, any column not present in the column list is assigned NULL. The setting for the LOAD TABLE DEFAULTS parameter applies to all column DEFAULT values, including AUTOINCREMENT.
All constraint violations that occur during a LOAD TABLE operation as a result of inserting DEFAULT values apply towards any user specified IGNORE CONSTRAINT and MESSAGE LOG/ROW LOG option.
Column default values of UTC TIMESTAMP and CURRENT UTC TIMESTMAP are not supported by Sybase IQ. An error is reported every time an attempt is made to insert or update the default value of a column of this type.
Column DEFAULT values defined on base tables are not propagated to joins in which these tables participate.
Column DEFAULT values are not permitted on tables that participate in join indexes and Sybase IQ generates an error, if you attempt to define a DEFAULT value on such a table. This rule is similar to support for the AUTOINCREMENT default value.
If a column on which a default value is defined is added to a table, then all rows of the new column are populated with that default value.
Changing the default value of an existing column in a table does not change any existing values in the table.
See the individual sections for specific default value types later in this section for more information on defining and inserting column default values. Also see “Special values” in Chapter 3, “SQL Language Elements” of the Sybase IQ Reference Manual for more information on the special values that can be used in default column value expressions.
