Oracle7 Server SQL Reference

DISABLE clause

Purpose

To disable an integrity constraint or all triggers associated with a table:

If you disable an integrity constraint, Oracle7 does not enforce it. However, disabled integrity constraints appear in the data dictionary along with enabled integrity constraints.

If you disable a trigger, Oracle7 does not fire it if its triggering condition is satisfied.

Prerequisites

A DISABLE clause that disables an integrity constraint can appear in either a CREATE TABLE or ALTER TABLE command. To disable an integrity constraint, you must have the privileges necessary to issue one of these commands. For information on these privileges, see the CREATE TABLE command

and the ALTER TABLE command

For an integrity constraint to appear in a DISABLE clause, one of the following conditions must be true:

the integrity constraint must be defined in the containing statement

the integrity constraint must already have been defined and enabled in previously issued statements

A DISABLE clause that disables triggers can only appear in an ALTER TABLE statement. To disable triggers with a DISABLE clause, you must have the privileges necessary to issue this statement. For information on these privileges, see the ALTER TABLE command

. Also, the triggers must be in your own schema or you must have ALTER ANY TRIGGER system privilege.

Syntax

UNIQUE Keywords and Parameters

disables the UNIQUE constraint defined on the specified column or combination of columns.

PRIMARY KEY

disables the table's PRIMARY KEY constraint.

CONSTRAINT

disables the integrity constraint with the name constraint.

CASCADE

disables any integrity constraints that depend on the specified integrity constraint. To disable a primary or unique key that is part of a referential integrity constraint, you must specify this option.

ALL TRIGGERS

disables all triggers associated with the table. This option can only appear in a DISABLE clause in an ALTER TABLE statement, not a CREATE TABLE statement.

Usage Notes

You can use the DISABLE clause to disable:

a single integrity constraint

all triggers associated with a table

To disable a single trigger, use the DISABLE option of the ALTER TRIGGER command.

How to Disable Integrity Constraints

You can disable an integrity constraint by naming it in a DISABLE clause of either a CREATE TABLE or ALTER TABLE statement. You can define an integrity constraint with a CONSTRAINT clause and disable it with a DISABLE clause together in the same statement. You can also define an integrity constraint in one statement and subsequently disable it in another.

You can also disable an integrity constraint with the DISABLE keyword in the CONSTRAINT clause that defines the integrity constraint. For information on this keyword, see the CONSTRAINT clause .

How Oracle7 Disables Integrity Constraints If you disable an integrity constraint, Oracle7 does not enforce it. If you define an integrity constraint and disable it, Oracle7 does not apply it to existing rows of the table, although Oracle7 does store it in the data dictionary along with enabled integrity constraints. Also, Oracle7 can execute Data Manipulation Language statements that change table data and violate a disabled integrity constraint.

If you disable a UNIQUE or PRIMARY KEY constraint that was previously enabled, Oracle7 drops the index that enforces the constraint.

You can enable a disabled integrity constraint with the ENABLE clause.

Disabling Referenced Keys in Referential Integrity Constraints To disable a UNIQUE or PRIMARY KEY constraint that identifies the referenced key of a referential integrity constraint, you must also disable the foreign key. To disable a constraint and all its dependent constraints, use the CASCADE option of the DISABLE clause.

You cannot enable a foreign key that references a unique or primary key that is disabled.

Example I

The following statement creates the DEPT table and defines a disabled PRIMARY KEY constraint:

CREATE TABLE dept

	(deptno  NUMBER(2)  PRIMARY KEY,

	 dname   VARCHAR2(10),

	 loc     VARCHAR2(9) )

	DISABLE PRIMARY KEY

Since the primary key is disabled, you can add rows to the table that violate the primary key. You can add departments with null department numbers or multiple departments with the same department number.

Example II

The following statement defines and disables a CHECK constraint on the EMP table:

ALTER TABLE emp

	ADD (CONSTRAINT check_comp  CHECK (sal + comm <= 5000) )

 	DISABLE CONSTRAINT check_comp

The constraint CHECK_COMP ensures that no employee's total compensation exceeds $5000. Since the constraint is disabled, you can increase an employee's compensation above this limit.

Example III

Consider a referential integrity constraint involving a foreign key on the combination of the AREACO and PHONENO columns of the PHONE_CALLS table. The foreign key references a unique key on the combination of the AREACO and PHONENO columns of the CUSTOMERS table. The following statement disables the unique key on the combination of the AREACO and PHONENO columns of the CUSTOMERS table:

ALTER TABLE customers 
	DISABLE UNIQUE (areaco, phoneno) CASCADE

Since the unique key in the CUSTOMERS table is referenced by the foreign key in the PHONE_CALLS table, you must use the CASCADE option to disable the unique key. This option disables the foreign key as well.

How to Disable Triggers

You can disable all triggers associated with the table by using the ALL TRIGGERS option in a DISABLE clause of an ALTER TABLE statement. After you disable a trigger, Oracle7 does not fire the trigger when a triggering statement meets the condition of the trigger restriction.

Example IV

The following statement disables all triggers associated with the EMP table:

ALTER TABLE emp

	DISABLE ALL TRIGGERS