Using the INDEX DISABLED keywords in a foreign key definition

By including the optional INDEX DISABLED keywords when you define a foreign key constraint, you prevent DML operations on the table from using the index that the database server associates with the foreign key. If you include the INDEX DISABLED keywords, they must be the last specification in the ALTER TABLE statement.

Defining foreign key constraints in the ALTER TABLE statement

To add a foreign key constraint, you must have the References privilege on either the referenced columns or the child table. If you own the parent table or have the Alter privilege on the parent table, you can create a foreign key constraint on that table and specify yourself as the owner of the constraint. When you hold the DBA privilege, you can create foreign key constraints for other users.

When the ALTER TABLE ADD CONSTRAINT statement places a foreign key constraint on a column or on a set of columns that reference a child table, and no referential constraint or user-defined index already exists on that column or on that set of columns, the database server creates an internal B-tree index on the specified column or set of columns. If a user-created index already exists on that column or set of columns, the constraint shares the existing index.

If the ALTER TABLE ADD CONSTRAINT statement defines more than one foreign key constraint on the same table, each constraint requires its own REFERENCES clause, and the INDEX DISABLED keywords can be specified (or omitted) for each constraint.

This INDEX DISABLED option is valid in ALTER TABLE ADD CONSTRAINT statements issued from updatable secondary servers in cluster environments.

Circumstances where a foreign key index can reduce performance

Although referential constraints protect data integrity, in some contexts the user-defined or system-generated B-tree index that the database server associates with a foreign key constraint can reduce the efficiency of data manipulation operations on tables that are very large. If there are no deletes from the parent table, the index is not used to look up rows in the child table for cascading deletes. If no queries use this index on the child table. In this scenario, the index is simply not needed, but it imposes unnecessary overhead in operations that update, delete, or insert rows into the child table. A data warehousing application on a child table with millions of rows could require fewer resources if the index that corresponds to the foreign key constraint were disabled.

In these cases, the INDEX DISABLED keyword option to the ALTER TABLE ADD CONSTRAINT statement offers a mechanism for defining a foreign key constraint but avoiding the overhead of the large associated b-tree index.

When you include the INDEX DISABLED keywords at the end of the constraint definition, the database server disables the system-generated index, if no appropriate user-defined index already exists. If a user-defined index on the foreign key column (or set of columns) of the child table already exists, the database server disables that index. Subsequent DML operations on the child table are accomplished without an index, and minimal system resources are needed for maintenance and storage of the disabled index.

Effects of the INDEX DISABLED keywords

When you include the INDEX DISABLED keywords at the end of the constraint definition, the database server disables the system-generated index, if no appropriate user-defined index already exists. If a user-defined index on the foreign key column (or set of columns) of the child table already exists, the database server disables that index. Subsequent foreign key enforcement during DML operations on the child or parent table are accomplished without this disabled index, and minimal system resources are needed for maintenance and storage of the disabled index.

These are the actions of the database server when you successfully add a foreign key constraint with the INDEX DISABLED option:

The index associated with the foreign key constraint is identified.
That index is disabled, and marked as disabled in the sysobjstate table of the system catalog.
The physical index is dropped from the database.
The sysfragments system catalog table is updated to show no storage allocation for that index.

The INDEX DISABLED keywords have no effect on foreign key constraint that you define. The database server enforces that constraint, and issues an error if any subsequent operation on the child table or on the parent table violates the specified foreign key constraint.

The following restrictions apply to the INDEX DISABLED keywords in constraint definitions:

The INDEX DISABLED option is valid only in foreign key definitions.
Only the ALTER TABLE ADD CONSTRAINT statement supports this syntax. The CREATE TABLE or ALTER TABLE MODIFY COLUMN statements return an exception if a foreign key constraint definition includes the INDEX DISABLED keywords.
If the index used by the foreign key is in use by another constraint, the database server returns an error.
If you include the DISABLED keyword in the constraint definition to disable the foreign key constraint, the database server returns an error if you also specify the INDEX DISABLED keywords, as in the following example.

 ALTER TABLE child ADD 
   CONSTRAINT(FOREIGN KEY(x1) REFERENCES parent(c1) 
      CONSTRAINT cons_child_x1 DISABLED INDEX DISABLED);

To correct the error in ALTER TABLE ADD CONSTRAINT example above, you must either drop the first DISABLED keyword, or else drop the INDEX DISABLED keywords.

Example of creating a foreign key constraint with INDEX DISABLED

Suppose that the parent table and the child table in the following example have a primary key and foreign key dependency, and that the data stored in these tables satisfies the following conditions:

The parent table has only a few rows.
The child table has millions of rows.
The foreign key columns in the child table have only few distinct possible values, based on the primary key of the parent table.

This example shows how to use the INDEX DISABLED keyword option of the ALTER TABLE ADD CONSTRAINT statement.

CREATE TABLE parent(c1 INT, c2 INT, c3 INT);
CREATE UNIQUE INDEX idx_parent_c1 ON parent(c1);
ALTER TABLE parent ADD 
   CONSTRAINT PRIMARY KEY(c1) 
      CONSTRAINT cons_parent_c1;
CREATE TABLE child(x1 INT, x2 INT, x3 VARCHAR(32));
CREATE INDEX idx_child_x1 ON child(x1);

ALTER TABLE child ADD 
   CONSTRAINT(FOREIGN KEY(x1) REFERENCES parent(c1) 
      CONSTRAINT cons_child_x1 INDEX DISABLED);

In the example above,

cons_parent_c1 is a primary key constraint on the parent table,
cons_child_x1 is a foreign key constraint on the child table,
idx_parent_c1 is a unique index shared by the cons_parent_c1 constraint,
and idx_child_x1 is an index shared by the cons_child_x1 constraint.

Data manipulation language operations like UPDATE, DELETE, INSERT, and MERGE on the child table cannot use the idx_child_x1 index that is shared by with the foreign key constraint, because that index is now disabled.

For some tables that have a primary key and foreign key dependencies, however, the query optimizer might choose other indexes on the child table, based on WHERE clause predicates, in execution plans.

As indicated above, use of the INDEX DISABLED option in the foreign key definition can improve performance only when the child table is very large, typically in the context of data warehouse applications. This syntax option is not recommended for DML operations on small tables.