Constraint WITH_Clause--Define Constraint Index Options
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.]tablename[(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.]tablename[(column_name {,column_name})][referential_actions][WITH constraint_with_clause]
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).
• PAGE_SIZE = n
• NO INDEX
• INDEX = BASE TABLE STRUCTURE
• INDEX = index_name
• STRUCTURE = HASH | BTREE | ISAM
• FILLFACTOR = n
• MINPAGES = n
• MAXPAGES = n
• LEAFFILL = n
• NONLEAFFILL = n
• ALLOCATION = n
• EXTEND = n
• LOCATION = (location_name{, location_name})
Note: The NO INDEX and INDEX = BASE TABLE STRUCTURE options cannot be used in combination with any other constraint WITH option.
No Index 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, may use some other user-defined index on the same columns; or it may 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:
• An alternate index on referencing columns is available
• There are very few rows in the referencing table (as in a prototype application)
• Deletes and updates are rarely (if ever) performed on the referenced table
Index = Base Table Structure Option
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 tablename 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.
Index = Index_Name Option
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 cases 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.
Last modified date: 01/30/2023