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.)
A.
It fails if any existing row violates the constraint.
B.
It does not validate the existing data in the table.
C.
It enables the constraint to be enforced at the end of each transaction.
D.
It prevents insert, update, and delete operations on the table while the constraint is in the
process of being enabled.
Constraints state – a total of four state
a. Enable validate- requirements of old and new data must satisfy the constraint rule – when the rule is not being allowed to perform any DML on the table Operations
b. Enable novalidate- existing data can not satisfy, but the new data must satisfy
c. Disable validate- not allow any DML operation on the table on the primary key and unique constraints, it will delete the unique index, but Constraint rule is still valid
d. Disable novalidate- data does not satisfy the constraint rules, primary key and unique constraints, it will delete the unique index
INITIALLY IMMEDIATE initialization immediate execution – constraint test at the end of each statement (Or when calling set constraint immediate statement)
INITALLY DEFERRED initialization delay implementation until after the transaction has been completed before the check constraint
SQL> create table t (x int constraint check_x check (x> 0) deferrable initially immediate,
y int constraint check_y check (y> 0) deferrable initially deferred)
SQL> insert into t values (-1,1);
insert into t values (-1,1); 0ERROR at line 1: ORA-02290: checkconstraint (OPS $ TKYTE.CHECK_X) violated
Since CHECK_X is initialized to delay constraint but executed immediately, so the line was immediately refused. The CHECK_Y is different, it is not only delayed, but early
Initialized to delay the execution, which means until I COMMIT command to commit the transaction or that are executed immediately when the test status is set constraints.
At this point the database transaction is rolled back, because a constraint violation caused the failure of the COMMIT statement.
These statements illustrate the initialization immediately doing and the difference between the initialization delay constraint.
initially (initialization) section specifies Oracle when it will be the default constraint checking – is [immediate (immediate execution)] at the end of the statement,
Or at the end of the transaction [deferred (delayed execution)].
Also described deferred (deferrable) What is the use clause.
What are the practical constraints delayed the use? There are many.
It is mainly used for materialized views (snapshots). These views will be used to view refresh delay constraints.
Materialized view refresh process, it may undermine the integrity, and will not step test constraints.
But the implementation of COMMIT, data integrity, no problem, but also to satisfy the constraints.
No delay constraint, the constraint may cause the materialized view refresh process is not successful.
Another common reason for using the delay constraint is that when predicting the need for updating the parent / child relationship in the primary key, it helps cascading updates.
If you set the foreign key can be delayed, but is initialized to be executed immediately, then you can set all deferrable constraints.
The parent key is updated to a new value – integrity constraints to this child relationship can not be verified. The child foreign key is updated to the new value.
COMMIT– as long as all the child records are affected by the update to an existing parent record, this command can be executed successfully.
B is incorrect. VALIDATE ensures that existing data conforms to the constraint
During enable process, cannot DML into the table