1Z0-052 · Question #92
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…
The correct answer is A. It fails if any existing row violates the constraint. D. It prevents insert, update, and delete operations on the table while the constraint is in the process of. ALTER TABLE with ENABLE VALIDATE checks all existing rows against the constraint and holds a lock that blocks DML on the table during that validation.
Question
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? (Choose two.)
Options
- AIt fails if any existing row violates the constraint.
- BIt does not validate the existing data in the table.
- CIt enables the constraint to be enforced at the end of each transaction.
- DIt prevents insert, update, and delete operations on the table while the constraint is in the process of
How the community answered
(36 responses)- A75% (27)
- B19% (7)
- C6% (2)
Why each option
ALTER TABLE with ENABLE VALIDATE checks all existing rows against the constraint and holds a lock that blocks DML on the table during that validation.
The VALIDATE keyword causes Oracle to scan every existing row in the table; if even one row violates the constraint, the command fails with an error and the constraint remains in its previous disabled state.
ENABLE VALIDATE explicitly validates existing data - it is ENABLE NOVALIDATE that enables the constraint for future DML while skipping validation of rows already in the table.
INITIALLY IMMEDIATE means the constraint is evaluated at the end of each individual statement, not at the end of a transaction; deferring enforcement to transaction commit requires INITIALLY DEFERRED or DEFERRABLE INITIALLY DEFERRED.
During the ENABLE VALIDATE operation Oracle acquires a share lock on the table, which prevents any INSERT, UPDATE, or DELETE statements from executing against that table until the constraint validation completes and the lock is released.
Concept tested: Oracle constraint ENABLE VALIDATE state effects
Source: https://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm
Topics
Community Discussion
No community discussion yet for this question.