1.1

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).
• PRIMARY KEY— Specifies a column (or multiple columns if specified in a table constraint) that uniquely
identifies a row in the table. The identified columns must be defined as NOT NULL.
• UNIQUE— Specifies that values in the column must be unique. NULL values are not allowed.
• FOREIGN KEY— Specifies that the values in the columns must correspond to values in referenced primary
key or unique columns or that they are NULL.
Note: If the foreign key consists of multiple columns and any column is NULL, then the whole key is
considered NULL. SQLFire permits the insert no matter what is on the non-null columns.
Note: SQLFire only supports ON DELETE RESTRICT for foreign key references, and can be
optionally specified that way. SQLFire checks dependent tables for foreign key constraints. If any row
in a dependent table violates a foreign key constraint, the transaction is rolled back and an exception is
thrown. SQLFire does not support cascade deletion.
• CHECK— Specifies rules for values in a column, or specifies a wide range of rules for values when included
as a table constraint. The CHECK constraint has the same format and restrictions for column and table constraints.
Column constraints and table constraints have the same function; the difference is in where you specify them.
Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE, CHECK, or
FOREIGN KEY constraint definition. Column-level constraints (except for check constraints) refer to only one
column.
If you do not specify a name for a column or table constraint, then SQLFire generates a unique name.
Column Constraint
A column constraint applies a rule to a single column in the table. You include a column constraint as part of
the column definition.
Syntax
{
NOT NULL |
[ [ CONSTRAINT constraint-name ]
{
CHECK (
CHECK Constraint Search Condition
) |
{
PRIMARY KEY |
UNIQUE |
REFERENCES table-name [ ( column-name [ , column-name ]* ) ]
[ ON DELETE RESTRICT ]
}
}
}
vFabric SQLFire User's Guide480
vFabric SQLFire Reference