The primary key, unique and referential constraint definitions can optionally include a WITH clause to describe the characteristics of the indexes that are created by Ingres to enforce the constraints.
The constraint_with_clause can be appended to both column- and table-level constraint definitions.
The column_constraint has the following syntax:
UNIQUE [WITH constraint_with_clause]
PRIMARY KEY [WITH constraint_with_clause]
REFERENCES [schema.]table_name[(column_name)][referential_actions][WITH constraint_with_clause]
The table_constraint has the following syntax:
UNIQUE (column_name {,column_name}) [WITH constraint_with_clause]
PRIMARY KEY (column_name {,column_name}) [WITH constraint_with_clause]
FOREIGN KEY (column_name {,column_name})
REFERENCES [schema.]table_name[(column_name {,column_name})][referential_actions][WITH constraint_with_clause]
Describes the index characteristics as one or more of the following options.
If options are used in combination, they must be separated by commas and enclosed in parentheses.
For example: WITH (STRUCTURE = HASH, FILLFACTOR = 70).
Note: The NO INDEX and INDEX = BASE_TABLE_STRUCTURE options cannot be used in combination with any other constraint WITH option.
The NO INDEX option indicates that no secondary index is created to support the constraint. This option is permissible for referential constraints only and results in no index being available to check the integrity of deletes and updates to the referenced table. The database procedures that perform the integrity checks still execute in the absence of these indexes. The query plan, however, can use some other user-defined index on the same columns; or it can resort to a full table scan of the referencing table, if there is no alternative.
To avoid poor performance, the NO INDEX option must be used only if:
The INDEX = BASE TABLE STRUCTURE option indicates that the base table structure of the constrained table be used for integrity enforcement, rather than a newly created secondary index. The base table structure must not be heap and must match the columns in the constraint definition. Because only non-heap base table structures can be specified using the MODIFY statement (after the table has been created), WITH INDEX = BASE TABLE STRUCTURE can be used only for table constraints defined with the ALTER TABLE (rather than the CREATE TABLE) statement.
The ALTER TABLE statement, which adds the constraint, must be preceded by the WITH INDEX = BASE TABLE STRUCTURE statement.
For example:
alter table table_name add constraint constraint_name
primary key(column(s))
with (index = base table structure)
which indicates that the uniqueness semantics enforced by the index are consistent with Ingres and ANSI rules.
The INDEX = index_name option can be used for several purposes. If the named index already exists and is consistent with the columns constrained by the constraint definition, no new index is created. If the named index does not exist, the generated index created for constraint enforcement uses the name, index_name. Finally, if more than one constraint in the same table definition specifies INDEX = index_name with the same index_name, an index is generated with that name and is shared among the constraints.
In a case where an existing index is used for a constraint or a single index is shared among several constraints, the key columns of the index and the columns of the constraints must be compatible.
All other constraint with options perform the same function as the corresponding WITH options of the CREATE INDEX statement and the index related WITH options of the CREATE TABLE...AS SELECT statement. They are limited, however, to those options documented above. For example, the KEY and COMPRESSION options of CREATE INDEX and CREATE TABLE...AS SELECT are not supported for constraint definition.
The two types of integrities for tables and their error-handling characteristics are as follows:
These integrities are specified at the time the table is created or altered. An attempt to update the table with a row containing a value that violates the constraint causes the DBMS Server to abort the entire statement and issue an error.
These integrities are specified after the table is created. An attempt to update the table with a row containing a value that violates the constraint causes the invalid row to be rejected. No error is issued.
The two types of integrities handle nulls differently: check constraints (created using the CREATE TABLE or ALTER TABLE statement) allow nulls by default, whereas integrities created using the CREATE INTEGRITY statement do not allow nulls by default.
In addition to table constraints, use rules to perform integrity checks when a table is updated.
Note: The CREATE TABLE and ALTER TABLE statements are the ANSI/ISO SQL-92-compliant methods for maintaining database integrity.