ALLBASE/SQL Reference Manual (36216-90216)

454 Chapter11
SQL Statements E - R
INSERT
defined with the NOT NULL attribute. Therefore ensure either that select
list items are not null for any NOT NULL target column, or that the NOT
NULL target columns have default values defined for them.
Description — INSERT Rows Defined by a SELECT Command (Type
2 Insert)
• You cannot use the ORDER BY clause in a Type 2 Insert.
• You cannot insert into a LONG column with this kind of INSERT operation.
• You cannot specify a LONG column in the QueryExpression in this kind of INSERT
operation, except in a long column or string function.
• If you omit any of the table's columns from the column name list, the INSERT command
places the default value of the respective column definitions in the omitted columns. For
columns with no default value, the null value is placed in the omitted columns. If the
table definition specifies NOT NULL for any of the omitted columns, the INSERT
command fails.
• If ALLBASE/SQL detects an error during this kind of INSERT operation, error handling
behavior is determined by the setting of the SET DML ATOMICITY and SET
CONSTRAINTS statements. Refer to the discussion of these statements in this chapter.
• Using the INSERT command with views requires that the views be based on updatable
queries. See "Updatability of Queries" in Chapter 3 , “SQL Queries.”
• A table on which a unique constraint is defined cannot contain duplicate rows.
• Under the default settings for the SET DML ATOMICITY and SET CONSTRAINTS
statements, integrity constraints on tables and views are enforced on a statement level
basis and if a constraint should be violated during processing of the insert, no rows are
inserted. However, the SET DML ATOMICITY and SET CONSTRAINTS statements both
override the default behavior. For more information, it is important that you refer to the
section "Error Conditions in ALLBASE/SQL" in Chapter 1 , “Introduction,”and the SET
DML ATOMICITY or the SET CONSTRAINTS statements in this chapter.
• Rows being inserted must not cause the search condition of the table check constraint to
be false and must cause the search condition of the view check constraint to be true.
• Rows being inserted in the table through a view having a WITH CHECK OPTION must
satisfy the check constraint of the view and any underlying views in addition to
satisfying any constraints of the table. Refer to the "Check Constraints" section of
Chapter 4 , “Constraints, Procedures, and Rules,” for more information on check
constraints.
• Values in referenced (primary key) columns must be inserted before values in
referencing (foreign key) columns. However, if you do a bulk insertion, inserting the
primary key rows after the foreign key rows does not cause an error message, as the
constraints are satisfied by the time you COMMIT WORK.
• BINARY and VARBINARY data can be inserted in character or hexadecimal format.
Character format requires single quotes and hexadecimal requires a 0x before the
value.