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:
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
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:
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:
Example IV
The following statement enables all triggers associated with the EMP table:
ALTER TABLE emp
ENABLE ALL TRIGGERS