OCP1Z0-047: constraint-delay constraint

Source: Internet
Author: User
Tags commit first row

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.