1.1.1

You can insert rows into the table using either the DEFAULT keyword, or by omitting the identity column from
the INSERT statement:
insert into greetings values (DEFAULT, 'hello');
insert into greetings(ch) values ('hi');
The values that SQLFire automatically generates for a GENERATED ALWAYS identity column are unique.
GENERATED BY DEFAULT Identity Columns
For a GENERATED BY DEFAULT identity column, SQLFire increments and uses a default value for an
INSERT only when no explicit value is given. To use the generated default value, either specify the DEFAULT
keyword when inserting into the identity column, or leave the identity column out of the INSERT column list.
In contrast to GENERATED ALWAYS identity columns, with a GENERATED BY DEFAULT column you
can specify an identity value to use instead of the generated default value. To specify a value, include it in the
INSERT statement.
For example, consider a table created using the statement:
create table greetings (i int generated by default as identity, ch char(50));
The following statement specifies the value "1" for the identity column:
insert into greetings values (1, 'hi');
These statements both use generated default values:
insert into greetings values (DEFAULT, 'hello');
insert into greetings(ch) values ('bye');
Although the automatically-generated values in a GENERATED BY DEFAULT identity column are unique, a
GENERATED BY DEFAULT column does not guarantee unique identity values for all rows in the table. For
example, in the above statements the rows containing "hi" and "hello" both have an identity value of "1." This
occurs because the generated column starts at "1" and the user-specified value was also "1."
To avoid duplicating identity values (for example, during an import operation), you can use the START WITH
clause to specify the first identity value that SQLFire should assign and increment. Or, you can use a primary
key or a unique constraint on the GENERATED BY DEFAULT identity column to check for and disallow
duplicates.
By default, the initial value of a GENERATED BY DEFAULT identity column is 1, and the value is incremented
by 1 for each INSERT. Use the optional START WITH clause to specify a new initial value. Use the optional
INCREMENT BY clause to change the increment value used during each INSERT.
See also Auto-Generated Columns on page 707 for information about limitations with identity columns.
CONSTRAINT Clause
A CONSTRAINT clause is an optional part of a CREATE TABLE or ALTER TABLE statement that defines
a rule to which table data must conform.
There are two types of constraint:
• Column ConstraintSyntax refer to a single column in the table and do not specify a column name (except check
constraints). They refer to the column that they follow.
• Table ConstraintSyntax refer to one or more columns in the table. Table-level constraints specify the names
of the columns to which they apply. Table-level CHECK constraints can refer to 0 or more columns in the
table.
Column and table constraints include:
• NOT NULL— Specifies that a column cannot hold NULL values (constraints of this type are not nameable).
vFabric SQLFire User's Guide494
vFabric SQLFire Reference