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.]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]
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).
• 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.