In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.