1Z0-052 · Question #94
Examine the following statement that is used to modify the primary key constraint on the SALES table: SQL> ALTER TABLE SALES MODIFY CONSTRAINT pk DISABLE VALIDATE; Which three statements are true…
The correct answer is A. The constraint remains valid. B. The index on the constraint is dropped. C. It allows the loading of data into the table using SQL *Loader. DISABLE VALIDATE drops the associated unique index and prevents all standard DML, but it preserves the validated constraint metadata and permits SQL*Loader direct-path loads.
Question
Examine the following statement that is used to modify the primary key constraint on the SALES table:
SQL> ALTER TABLE SALES MODIFY CONSTRAINT pk DISABLE VALIDATE; Which three statements are true regarding the above command? (Choose three.)
Options
- AThe constraint remains valid.
- BThe index on the constraint is dropped.
- CIt allows the loading of data into the table using SQL *Loader.
- DNew data conforms to the constraint, but existing data is not checked.
- EIt allows the data manipulation on the table using INSERT/UPDATE/DELETE SQL statements.
How the community answered
(52 responses)- A67% (35)
- D12% (6)
- E21% (11)
Why each option
DISABLE VALIDATE drops the associated unique index and prevents all standard DML, but it preserves the validated constraint metadata and permits SQL*Loader direct-path loads.
The VALIDATE clause means Oracle confirmed that all existing rows satisfy the primary key constraint before disabling it, so the constraint is marked valid even though it is no longer actively enforced.
When a primary key or unique constraint is disabled, Oracle automatically drops the unique index that was enforcing it, because the index is only needed when the constraint is enabled.
DISABLE VALIDATE prevents standard INSERT/UPDATE/DELETE DML on the table, but SQL*Loader's direct-path load mechanism bypasses the SQL layer and the constraint engine, making it the intended method for bulk-loading data into tables held in this state.
This describes the ENABLE NOVALIDATE state - where new rows must conform but existing data is not checked; DISABLE VALIDATE does the opposite by validating existing data while blocking all new DML.
DISABLE VALIDATE explicitly prevents INSERT, UPDATE, and DELETE operations on the table; any attempt to perform standard DML will result in an error.
Concept tested: Oracle constraint DISABLE VALIDATE state behavior
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/constraint.html
Topics
Community Discussion
No community discussion yet for this question.