Oracle7 Server SQL Reference

ENABLE clause

Purpose

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

If you enable a constraint, Oracle7 enforces it by applying it to all data in the table. All table data must satisfy an enabled constraint.

If you enable a trigger, Oracle7 fires the trigger whenever its triggering condition is satisfied.

Prerequisites

An ENABLE clause that enables an integrity constraint can appear in either a CREATE TABLE or ALTER TABLE statement. To enable a constraint in this manner, you must have the privileges necessary to issue one of these statements. For information on these privileges, see the CREATE TABLE command

or the ALTER TABLE command

If you enable a UNIQUE or PRIMARY KEY constraint, Oracle7 creates an index on the columns of the unique or primary key in the schema containing the table. To enable such a constraint, you must have the privileges necessary to create the index. For information on these privileges, see the CREATE INDEX command .

If you enable a referential integrity constraint, the referenced UNIQUE or PRIMARY KEY constraint must already be enabled.

For an integrity constraint to appear in an ENABLE 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 disabled in a previously issued statement

An ENABLE clause that enables triggers can appear in an ALTER TABLE statement. To enable triggers with the ENABLE 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

Keywords and Parameters

UNIQUE

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

PRIMARY KEY

enables the table's PRIMARY KEY constraint.

CONSTRAINT

enables the integrity constraint named constraint.

USING INDEX

specifies parameters for the index Oracle7 creates to enforce a UNIQUE or PRIMARY KEY constraint. Oracle7 gives the index the same name as the constraint. You can choose the values of the INITRANS, MAXTRANS, TABLESPACE, STORAGE, and PCTFREE parameters for the index. For information on these parameters, see the CREATE TABLE command .

Only use these parameters when enabling UNIQUE and PRIMARY KEY constraints.

EXCEPTIONS INTO

identifies a table into which Oracle7 places information about rows that violate the integrity constraint. The table must exist before you use this option. If you omit

schema, Oracle7 assumes the exception table is in your own schema. The exception table must be on your local database.

ALL TRIGGERS

enables all triggers associated with the table. You can only use this option in an ENABLE clause in an ALTER TABLE statement, not a CREATE TABLE statement.

Usage Notes

You can use the ENABLE clause to enable either:

a single integrity constraint

all triggers associated with a table

To enable a single trigger, use the ENABLE option of the ALTER TRIGGER command.

How to Enable Integrity Constraints

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

You can also enable an integrity constraint by including the ENABLE keyword in CONSTRAINT clause that defines the integrity constraint. For information on this keyword, see the CONSTRAINT clause .

If you define an integrity constraint and do not explicitly enable or disable it, Oracle7 enables it by default.

How Oracle7 Enforces Integrity Constraints When you attempt to enable an integrity constraint, Oracle7 applies the integrity constraint to any existing rows in the table:

If all rows in the table satisfy the integrity constraint, then Oracle7 enables the integrity constraint.

If any row in the table violates the integrity constraint, the integrity constraint remains disabled. Oracle7 returns an error message indicating the integrity constraint is still disabled.

Once an integrity constraint is enabled, Oracle7 applies the integrity constraint whenever an INSERT, UPDATE, or DELETE statement tries to change table data:

If the new data satisfies the integrity constraint, then Oracle7 executes the statement.

If the new data violates the integrity constraint, then Oracle7 does not execute the statement. Instead, Oracle7 generates an error message indicating the integrity constraint violation.

How to Identify Exceptions An exception is a row in a table that violates an integrity constraint. You can request that Oracle7 identify exceptions to an integrity constraint. If you specify an exception table in your ENABLE clause, Oracle7 inserts a row into the exception table for each exception. A row of the exception table contains the following information:

the ROWID of the exception

the name of the integrity constraint

the schema and name of the table

A definition of a sample exception table named EXCEPTIONS appears in a SQL script available on your distribution media. Your exception table must have the same column datatypes and lengths as the sample. The common name of this script is UTLEXCPT.SQL , although its exact name and location may vary depending on your operating system. You can request that Oracle7 send exceptions from multiple enabled integrity constraints to the same exception table.

To specify an exception table in an ENABLE clause, you must have the privileges necessary to insert rows into the table. For information on these privileges, see the INSERT command . To examine the identified exceptions, you must have the privileges necessary to query the exceptions table. For information on these privileges, see the SELECT command .

If a CREATE TABLE statement contains both the AS clause and an ENABLE clause with the EXCEPTIONS option, Oracle7 ignores the EXCEPTIONS option. If there are any exceptions, Oracle7 does not create the table and returns an error message.

Example I

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

CREATE TABLE dept

	(deptno  NUMBER(2)      PRIMARY KEY,

	 dname   VARCHAR2(10),

	 loc     VARCHAR2(9) )

	TABLESPACE user_a

	ENABLE PRIMARY KEY USING INDEX INITRANS 3

	                   STORAGE (INITIAL 10K   NEXT 10K 
	                            MINEXTENTS 2  MAXEXTENTS 10)

	                   TABLESPACE user_b 
	                   PCTFREE 5

Oracle7 enforces the PRIMARY KEY constraint with an index. The ENABLE clause specifies INITRANS, STORAGE parameters, TABLESPACE, and PCTFREE values for the data blocks of the index.

Example II

The following statement enables an integrity constraint named FK_DEPTNO in the EMP table:

ALTER TABLE emp

	ENABLE CONSTRAINT fk_deptno

		EXCEPTIONS INTO except_table

Each row of the EMP table must satisfy the constraint for Oracle7 to enable the constraint. If any row violates the constraint, the constraint remains disabled. Oracle7 lists any exceptions in the table EXCEPT_TABLE. You can query this table with the following statement:

SELECT *

	FROM except_table

The output of this query might look like this:

ROW_ID              OWNER  TABLE_NAME  CONSTRAINT

------------------  -----  ----------  ---------- 0000346A.0001.0003  SCOTT  EMP         FK_DEPTNO

You can also identify the exceptions in the EMP table with the following statement:

SELECT emp.*

	FROM emp, except_table

	WHERE emp.row_id except_table.row_id

	  AND except_table.table_name = 'EMP'

	  AND except_table.constraint = 'FK_DEPTNO'

If there are exceptions to the FK_DEPTNO constraint, the output of this query might look like this:

EMPNO ENAME    JOB    MGR      HIREDATE   SAL      COMM    DEPTNO

---- -------- ------ -------- ---------- -------- -------- ------- 8001 JACK     CLERK       7788 25-AUG-92     1100               70

Example III

The following statement tries to enable two constraints on the EMP table:

ALTER TABLE emp

	ENABLE UNIQUE (ename)

 	ENABLE CONSTRAINT nn_ename

The preceding statement has two ENABLE clauses:

The first enables a UNIQUE constraint on the ENAME column.

The second enables the constraint named NN_ENAME.

In this case, Oracle7 only enables the constraints if both are satisfied by each row in the table. If any row violates either constraint, Oracle7 returns an error message and both constraints remain disabled.

How to Enable Triggers

You can enable all triggers associated with the table by including the ALL TRIGGERS option in an ENABLE clause of an ALTER TABLE statement. After you enable a trigger, Oracle7 fires the trigger whenever a triggering statement is issued that meets the condition of the trigger restriction. When you create a trigger, Oracle7 enables it automatically.

Example IV

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

ALTER TABLE emp

	ENABLE ALL TRIGGERS