Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

The State and Verification Mechanism of Oracle constraints

2025-01-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

I. status of Oracle constraints

There are four states of Oracle integrity constraints, which are ENABLE, DISABLE, VALIDATE and NOVALIDATE.

ENABLE indicates that Oracle will check whether the data in the database to be inserted or updated meets the constraints

DISABLE indicates that rows that violate constraints can be stored in the table

VALIDATE indicates that the database verifies whether the existing data in the table meets the constraint.

NOVALIDATE means that the database does not verify that the data that already exists in the table meets the constraint.

The default constraint states of Oracle are ENABLE and VALIDATE.

Let's take a look at the summary officially given by Oracle:

Modified DataExisting DataSummary

ENABLE

VALIDATE

Existing and future data must obey the constraint. An attempt to apply a new constraint to a populated table results in an error if existing rows violate the constraint.

ENABLE

NOVALIDATE

The database checks the constraint, but it need not be true for all rows. Thus, existing rows can violate the constraint, but new or modified rows must conform to the rules.

DISABLE

VALIDATE

The database disables the constraint, drops its index, and prevents modification of the constrained columns.

DISABLE

NOVALIDATE

The constraint is not checked and is not necessarily true.

The following uses an example to test each state:

Create a test table

Zx@ORA11G > create table T1 (id number,name varchar2 (10), address varchar2 (10)), Table created.zx@ORA11G > insert into T1 values, 1 row created.zx@ORA11G > commit;Commit complete.

1. Test the status of ENABLE and VALIDATE

Zx@ORA11G > alter table T1 add constraint t1_uk unique (id); alter table T1 add constraint t1_uk unique (id) * ERROR at line 1:ORA-02299: cannot validate (ZX.T1_UK)-duplicate keys found

Because there are duplicate values in the id column, the state of creating the constraint t1_uk is ENABLE, and VALIDATE validates the data that already exists in the table, so the creation of the constraint is not successful. Delete the duplicate data in the table and create the constraint again.

Zx@ORA11G > delete from T1 where id=1 and name='zq';1 row deleted.zx@ORA11G > commit;Commit complete.zx@ORA11G > alter table T1 add constraint t1_uk unique (id); Table altered.zx@ORA11G > select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1' TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED -T1 T1_UK U NOT DEFERRABLE ENABLED VALIDATED

After the creation is completed, the data inserted into id=1 will report an error, indicating that the constraint status is ENABLE.

Zx@ORA11G > insert into T1 values; insert into T1 values: unique constraint (ZX.T1_UK) violated

2. Test the status of ENABLE and DISABLED

Zx@ORA11G > select * from T1; ID NAME ADDRESS- 1 zx hb 2 wl sd 1 zq jxzx@ORA11G > alter table T1 add constraint t1_uk unique (id) enable novalidate Alter table T1 add constraint t1_uk unique (id) enable novalidate * ERROR at line 1:ORA-02299: cannot validate (ZX.T1_UK)-duplicate keys found

An error is reported by creating a unique constraint directly because there are duplicate values. However, you can succeed by creating an index on the id column and then creating a unique constraint.

Zx@ORA11G > create index idx_t_id on T1 (id); Index created.zx@ORA11G > alter table T1 add constraint t1_uk unique (id) using index idx_t_id enable novalidate;Table altered.zx@ORA11G > select * from T1 ID NAME ADDRESS- 1 zx hb 2 wl sd 1 zq jxzx@ORA11G > select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1' TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED -T1 T1_UK U NOT DEFERRABLE ENABLED NOT VALIDATED

If there are duplicate values in the id column in the original table, you can still create a unique constraint because the state is specified as NOVALIDATE and the data already in the table is not validated. In addition, because the status is ENABLE, an error is reported by inserting duplicate values again:

Zx@ORA11G > insert into T1 values; insert into T1 values * ERROR at line 1:ORA-00001: unique constraint (ZX.T1_UK) violated

3. Test the status of DISABLE and VALIDATE

Zx@ORA11G > select * from T1; ID NAME ADDRESS- 1 zx hb 2 wl sdzx@ORA11G > alter table T1 add constraint t1_uk unique (id) using index idx_t_id disable validate;Table altered.zx@ORA11G > select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1' TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED -T1 T1_UK U NOT DEFERRABLE DISABLED VALIDATEDzx@ORA11G > insert into T1 values (1 'zq','jx') Insert into T1 values * ERROR at line 1:ORA-25128: No insert/update/delete on table with constraint (ZX.T1_UK) disabled and validated

Additions, deletions and modifications are not allowed in DISABLE and VALIDATE.

4. Test the status of DISABLE and NOVALIDATE

Zx@ORA11G > select * from T1; ID NAME ADDRESS- 1 zx hb 2 wl sd 1 zq jxzx@ORA11G > alter table T1 add constraint t1_uk unique (id) using index idx_t_id disable novalidate Table altered.zx@ORA11G > select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1' TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED -T1 T1_UK U NOT DEFERRABLE DISABLED NOT VALIDATEDzx@ORA11G > insert into T1 values (2 'yc','bj') 1 row created.zx@ORA11G > commit;Commit complete.zx@ORA11G > select * from T1; ID NAME ADDRESS- 1 zx hb 2 wl sd 1 zq jx 2 yc bj

The constraint status is DISABLE and NOVALIDATE, and the new data and old data are not validated.

Second, verification mechanism

1. Two kinds of verification opportunities.

The constraints of Oracle can be divided into two types according to the timing of verification.

Case 1. It will be validated immediately when each insert statement is executed, and if the constraint validation fails, the sql statement will fail.

Case 2. Insert statements is not validated, it is validated at commit, and if validation fails, the entire Transaction is rolled back.

Classification of 2.constraints

Accordingly, the constraints of oracle can also be divided into two categories.

One is not deferrable (which cannot be delayed). Only the timing of case1 verification can be performed in this case (instant verification)

The other is deferrable (which can be set to delay). In this case, the verification timing of case 1 or case2 can be performed. But it needs to be set up.

For the second defferable classification, it can also be divided into two subcategories.

One is initially immediate, which means case 1. 0 is executed by default.

The other is initially deferred, which means case2 is executed by default.

That is, it can be divided into three categories, as shown in the following figure:

2.1 、 not deferrable

This is the most common and simplest. If you do not specify the validation timing attribute when adding 1 constraint. It will be set to not deferrable by default. Since constraint cannot be validated with delay, there is no need to set its initial properties (which is actually initially immediate).

Clear the T1 table above and create a unique constraint

Zx@ORA11G > truncate table T1 * table truncated.zx@ORA11G > select * from T1 * no rows selectedzx@ORA11G > alter table T1 add constraint t1_uk unique (id) not deferrable;Table altered.zx@ORA11G > select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1' TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED -T1 T1_UK U NOT DEFERRABLE ENABLED VALIDATED

Constrained to NOT DEFERRABLE status, insert test data view status:

Zx@ORA11G > insert into T1 values, 1 row created.zx@ORA11G > insert into T1 values, 1 row created.zx@ORA11G > insert into T1 values, 1 insert into T1 values * ERROR at line 1:ORA-00001: unique constraint (ZX.T1_UK) violatedzx@ORA11G > select * from T1 ID NAME ADDRESS- 1 zx hb 2 wl sd

When inserting the third piece of data, an error is reported directly because of duplicate data, indicating that the time for verification is case1: immediate verification, but the previously inserted results will not be rolled back.

2.2, deferrable, initially immediate status

Zx@ORA11G > alter table T1 drop constraint T1 problems table altered.zx@ORA11G > alter table T1 add constraint t1_uk unique (id) deferrable initially immediate;Table altered.zx@ORA11G > select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1' TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED -T1 T1_UK U DEFERRABLE ENABLED VALIDATEDzx@ORA11G > insert into T1 values (1 'zx','hb') 1 row created.zx@ORA11G > insert into T1 values; 1 row created.zx@ORA11G > insert into T1 values; 1 insert into T1 values * ERROR at line 1:ORA-00001: unique constraint (ZX.T1_UK) violatedzx@ORA11G > select * from T1 ID NAME ADDRESS- 1 zx hb 2 wl sd

Insert the third data Times error because there is a duplicate value, indicating that the timing of verification is case1: instant verification, which is the same as the previous state. Then why set such a state? Let's execute the following statement:

Zx@ORA11G > set constraint t1_uk deferred;Constraint set.

The above statement does not change any of the properties of the constraint, but just switches to another mode

That is to say, it is initially in immediate mode, and then temporarily becomes deferred mode after executing the above statement.

Execute the previous insert statement again:

Zx@ORA11G > insert into T1 values, 1 row created.zx@ORA11G > insert into T1 values, 1 row created.zx@ORA11G > insert into T1 values, 1 row created.

The third item can also be inserted. Try commit below:

Zx@ORA11G > commit;commit*ERROR at line 1:ORA-02091: transaction rolled backORA-00001: unique constraint (ZX.T1_UK) violatedzx@ORA11G > select * from T1 leadership no rows selected

Commit Times error, query T1 table, there is no data, indicating that the whole transaction was rolled back. That is, case2: delayed verification. Perform the above three inserts again at this point:

Zx@ORA11G > insert into T1 values, 1 row created.zx@ORA11G > insert into T1 values, 1 row created.zx@ORA11G > insert into T1 values, 1 ERROR at line 1:ORA-00001 (unique constraint (ZX.T1_UK) violated); insert into T1 values

As you can see from the above results, an error is reported when the third row is inserted, indicating that the above set constraint statement only has the scope of the current transaction. At the end of the transaction, the constraint state returns to the original mode.

2.3 、 deferrable 、 initially deferred

With the second experiment above, you can easily understand this state.

Zx@ORA11G > alter table T1 drop constraint T1 problems table altered.zx@ORA11G > alter table T1 add constraint t1_uk unique (id) deferrable initially deferred;Table altered.zx@ORA11G > select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1' TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED -T1 T1_UK U DEFERRABLE ENABLED VALIDATEDzx@ORA11G > insert into T1 values (1 'zx','hb') 1 row created.zx@ORA11G > insert into T1 values (2 row created.zx@ORA11G > insert into T1 values); 1 row created.zx@ORA11G > commit;commit*ERROR at line 1:ORA-02091: transaction rolled backORA-00001: unique constraint (ZX.T1_UK) violatedzx@ORA11G > select * from T1 / no rows selected

Reference: http://blog.csdn.net/nvd11/article/details/12654691

Http://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT33337

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 296

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report