![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Object modes
Indexes, triggers, and constraints allow for different "modes" of operation. The three basic modes of operation include:
- Enabled
- Disabled
- Filtering (only available on constraints and unique indexes)
If an object is set to "disabled" mode, the database server stops using the object but does not remove the object from the database.
The SET SQL statement is used to change the
mode of an object.
Listing 33. Example
SET SQL statement for disable/enableSET INDEXES cust_idx DISABLED; SET INDEXES cust_idx ENABLED; SET CONSTRAINTS uniq_lname DISABLED; SET CONSTRAINTS uniq_lname ENABLED; |
More than one object of the same type can be specified in the same SQL statement:
SET INDEXES cust_idx, phone_idx DISABLED; |
When an index on a table is disabled, that index is no longer available for use as an access method for that table and no modifications to the table are synced up in the index. However, the index is still defined as an object in the database. To start using the index again, it needs to be enabled. When an index is enabled, it is completely rebuilt, as changes to the table while it was disabled are not reflected in the index.
When a constraint is set to disabled, that constraint is no longer used to verify the integrity of the data for the table on which it is defined. When a constraint is re-enabled, all data in the table must be verified against the constraint since some data may have been modified while it was disabled and might not meet the constraint. When re-enabling a constraint, if a row is found to violate the constraint, an error will be returned, and the constraint will not be re-enabled.
When a trigger is set to disabled, that trigger is not fired for any matching event. Once re-enabled, the trigger would then start firing for any future matching event.
Both constraints and unique indexes can also be set to FILTERING mode. FILTERING mode allows the constraint or index to be used. But if a modification to the table would violate the constraint or index, the row would be written to a violations table in the database, and the modification statement could keep running. In order for this to work successfully, a violations and diagnostics table must be started for the table where the constraint or index is set to filtering mode.
Example:
Listing 34. Example filtering mode
CREATE TABLE customer ( Name char(50), phone char(12), age int);
CREATE UNIQUE INDEX uniq_phone ON customer(phone);
SET INDEXES uniq_phone FILTERING WITHOUT ERROR;
START VIOLATIONS TABLE FOR customer;
INSERT INTO customer VALUES ("Flintstone", "110-555-1212", 28);
INSERT INTO customer VALUES ("Rubble","110-555-1212", 24);
|
The second insert in Listing 34 violates the unique constraint on phone
by trying to insert the same phone number for another row. The insert
would not error out (FILTERING WITHOUT ERROR), but would also not
insert the row into the customer table. Instead, it would insert the
row into the customer violations table (customer_vio) along with
diagnostics information for the problem into the customer diagnostics
table (customer_dia). To see any violation and diagnostic information,
run regular SELECT statements against the
violations and diagnostic tables.
Once the filtered object is taken out of filtering mode and placed back
in enabled mode, it is good practice to stop the violations table as
well. The STOP VIOLATIONS TABLE SQL
statement is used for that purpose.
Example continued from above:
Listing 35. Stop the violations table
SET INDEXES uniq_phone ENABLED; STOP VIOLATIONS TABLE FOR CUSTOMER; |
Note: Stopping the violations table will not drop the violations and diagnostics tables. To start the violations tables again for a given table, you first need to drop the existing violation and diagnostic tables for that table.
