OCP1Z0-047: constraint-delay constraint

This question is the test of delay in the constraints of the relevant knowledge points. In the table Cust set the primary key cust_id (only and Non-null), in the primary key set delay constraint, the so-called delay constraint is in DML when the database does not judge whether the data satisfies the constraint, when the commit to judge, if commit, When you do a DML operation (this is the data that is inserted, if it does not meet the constraint, it will do rollback)

In this question, the first line, the second line of data cust_id are 1, repeated, the submission will certainly be an error, the results will be rolled back. Corresponds to the first row, and the second row is not inserted into the table Cust.

The third line, the fourth line of cust_id 1 and 2, no duplicates, the commit is successfully inserted, will be permanently saved.

So you can see that the correct answer is: C

For more information on deferred constraints, refer to my blog:

http://blog.csdn.net/guoyjoe/article/details/8640270

Delay or immediate constraint

The immediate constraint is to immediately determine whether the user's modifications violate the constraint when each DML statement is executed, and return to the currently executing DML statement if the violation is returned. The constraints that we used to encounter were such immediate constraints.

Deferred constraints do not determine whether a constraint is violated at the end of a DML statement, but are then judged when the transaction is committed. If the constraint violates, the entire transaction is rolled back.

When you create a constraint, you have two options to set how the constraint is checked:

Initiallyimmediate: initially for immediate constraint.

Initially deferred: Originally a deferred constraint.

If the constraint is set to Initiallyimmediate, then the constraint is still immediate, but we can use the command to set the delay. And if the constraint is set up to initially deferred, the constraint is established, which is deferred.

Let's try the following:

gyj@ocm> drop table T8;  
      
Table dropped.  
      
Gyj@ocm> CREATE TABLE T8 (ID number () constraint T8_id_c check (id>=5)  
 2  initially deferred,namevarchar2 ());  
      
Gyj@ocm> INSERT INTO T8 values (1, ' gyj ');  
      
1 row created.  
can be inserted normally. The check of the constraint was postponed by  
      
Gyj@ocm> commit;  
Commit  
*  
ERROR at line 1:  
ora-02091:transaction rolled  
ora-02290:check constraint (GYJ. T8_id_c) violated