ALTER TABLE command with the ENABLE VALIDATE option effects of this command - Oracle Schema

Q.  A constraint in a table is defined with the INITIALLY IMMEDIATE clause. You executed the ALTER TABLE command with the ENABLE VALIDATE option to enable the constraint that was disabled.

What are the two effects of this command?
- Published on 13 Aug 15

a. It fails if any existing row violates the constraint
b. It does not validate the existing data in the table
c. It prevents insert, update, and delete operations on the table while the constraint is in the process of being enabled.
d. Both A & C
e. Both A & B

ANSWER: Both A & C

Related Content

 

    Discussion

  • Nirja Shah   -Posted on 25 Sep 15
    - You can also avoid rebuilding the index and eliminate redundant indexes by creating new primary key and unique constraints initially disabled.

    - Then create (or use existing) nonunique indexes to enforce the constraint.

    - Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.

    - ENABLE VALIDATE specifies that all old and new data also complies with the constraint.

    - An enabled validated constraint guarantees that all data is and will continue to be valid.

    - If any row in the table violates the integrity constraint, the constraint remains disabled and Oracle returns an error.

    - If all rows comply with the constraint, Oracle enables the constraint.

    - Subsequently, if new data violates the constraint, Oracle does not execute the statement and returns an error indicating the integrity constraint violation.

    - If you place a primary key constraint in ENABLE VALIDATE mode, the validation process will verify that the primary key columns contain no nulls.

    - To avoid this overhead, mark each column in the primary key NOT NULL before entering data into the column and before enabling the primary key constraint of the table.

    - ENABLE NOVALIDATE ensures that all new DML operations on the constrained data comply with the constraint.

    - This clause does not ensure that existing data in the table complies with the constraint and therefore does not require a table lock.

➨ Post your comment / Share knowledge


Enter the code shown above:

(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)
▲