Relationship and interaction between primary key and primary key index

The definition of a primary key: A collection of columns or columns that is used to uniquely identify a row in a table. Only one primary key is allowed on a table.

When we specify a primary key in the database, it is defined by a PRIMARY key constraint. When you create a PRIMARY key constraint, you need to have an appropriate index to match the implementation. Therefore, the purpose of this article is to summarize the relationship and interaction between the primary key and the index when creating the primary key constraint with different methods.

To match the description and testing of this article, first create the following test table:

CREATE TABLE Test (ID number,c1 varchar2 (8));

First, let's look at the most common ways to create PRIMARY key constraints and indexes:

ALTER TABLE t1 ADD constraint pk_test_id primary key (ID);

After the creation is complete, check the appropriate constraints and contents in the indexed view:

Sql> Select Constraint_name,constraint_type,table_name,status from user_constraints where table_name= ' TEST '; Constraint_name CO table_name STATUS------------------------------------------------PK_TEST_IDP TEST ENABLED

From the above, we have created a primary key constraint named "pk_test_id" whose current state is valid.

Sql> Select Index_name,uniqueness,table_name,status from user_indexes where table_name= ' TEST '; index_   nameuniqueness table_name STATUS----------------------------------------------------------------Pk_test_idunique TEST VALID

From the above, the database automatically creates a unique index with the same name for the PRIMARY KEY constraint.

If we were to close or delete the primary KEY constraint at this point, what would happen?

sql> ALTER TABLE TEST disable constraint pk_test_id; Table altered. Sql> Select Constraint_name,constraint_type,table_name,status from user_constraints where table_name= ' TEST '; Constraint_name CO table_name STATUS------------------------------------------------PK_TEST_IDP TEST disabledsql& Gt Select Index_name,uniqueness,table_name,status from user_indexes where table_name= ' TEST '; no rows selected

It is visible that when we close the primary KEY constraint, the index with the same name is automatically deleted.

What if we don't close or delete the primary KEY constraint, but instead close or delete the automatically created index of the same name?

Sql> ALTER TABLE test enable constraint pk_test_id; Table altered. Sql> Select Constraint_name,constraint_type,table_name,status from user_constraints where table_name= ' TEST '; Constraint_name CO table_name STATUS------------------------------------------------PK_TEST_IDP TEST ENABLEDSQL&G T Select Index_name,uniqueness,table_name,status from user_indexes where table_name= ' TEST '; index_nameuniqueness TABLE_ NAME STATUS----------------------------------------------------------------pk_test_idunique TEST VALID

First, we first restore the closed primary KEY constraint, find that the state of the constraint has returned to normal, and that the same name of the index has been rebuilt and restored. Now we will close or delete the index with the same name.

sql> ALTER index pk_test_id Unusable;index altered. Sql> Select Constraint_name,constraint_type,table_name,status from user_constraints where table_name= ' TEST '; Constraint_name CO table_name STATUS------------------------------------------------PK_TEST_IDP TEST ENABLEDSQL&G  T Select Index_name,uniqueness,table_name,status from user_indexes where table_name= ' TEST '; index_nameuniqueness TABLE_ NAME STATUS----------------------------------------------------------------pk_test_idunique TEST unusable

Visible, when the index is closed, the state of the primary key is still normal, except that the state of the index becomes unusable. However, if you insert data into the table at this point, you will get an error ORA-01502 as follows:

sql> INSERT INTO test values (1, ' a '); Insert to test values (1, ' a ') *error at line 1:ora-01502:index ' U1. pk_test_id ' or partition of such index is in unusable state

If we delete the index at this point, we will also get an error, indicating that the index is being used by the constraint and not allowed to delete.

Sql> Drop Index Pk_test_id;drop index pk_test_id *error at line 1:ora-02429:cannot Drop index used for enfor Cement of Unique/primary Key

From these two phenomena, it is also proved that the primary KEY constraint is completed with the index on the corresponding column.

At this point, we summarize the first method of creating constraints and indexes: When the index of the primary KEY constraint is created automatically when the primary key constraint is created, then closing or deleting the constraint automatically deletes the corresponding index, while closing the corresponding index does not affect the state of the primary key, but inserting the data into the table will cause an error (delete the data , as well as updates to the primary key column), and when an attempt is taken to delete the index, an error is also taken, and the index is not allowed to be deleted.

Second, when creating a primary key constraint, specify the appropriate index method.

When creating a primary key constraint, we can also specify an index. Here's how:

sql> drop table test purge; Table dropped. Sql> CREATE TABLE Test (ID number,c1 varchar2 (8)); Table created. Sql> ALTER TABLE TEST add constraint pk_test_id primary key (ID) using index (CREATE index ind_test_id on test (ID)); Table altered.

The first two SQL is the rebuild test environment to avoid the impact of the previous tests. The last SQL specifies a primary KEY constraint on the ID column and specifies that a non-unique index on that column is created.

Let's take a look at the relevant constraints and indexed views:

Sql> Select Constraint_name,constraint_type,table_name,status from user_constraints where table_name= ' TEST '; Constraint_name CO table_name STATUS------------------------------------------------PK_TEST_IDP TEST ENABLEDSQL&G T Select Index_name,uniqueness,table_name,status from user_indexes where table_name= ' TEST '; index_nameuniqueness TABLE_ NAME STATUS----------------------------------------------------------------ind_test_idnonunique TEST VALID

Note the name and uniqueness of the index in the last index information differs from the previous auto-creation of the primary key index. This indicates that a non-unique index can also be used in conjunction with the completion of a PRIMARY KEY constraint.

As in the previous test, we tried to close or delete the primary KEY constraint to see what the situation was?

sql> ALTER TABLE TEST disable constraint pk_test_id; Table altered. Sql> Select Constraint_name,constraint_type,table_name,status from user_constraints where table_name= ' TEST '; Constraint_name CO table_name STATUS------------------------------------------------PK_TEST_IDP TEST disabledsql&  Gt Select Index_name,uniqueness,table_name,status from user_indexes where table_name= ' TEST '; index_nameuniqueness TABLE_ NAME STATUS----------------------------------------------------------------ind_test_idnonunique TEST VALID

It is visible that the index is not affected except for the corresponding PRIMARY KEY constraint failure. This is different from the case where the primary key index is automatically created in front of it.

Next, we try to close or delete the corresponding index, whether the corresponding primary KEY constraint will be affected.

Sql> ALTER TABLE test enable constraint pk_test_id; Table altered. sql> ALTER index ind_test_id Unusable;index altered. Sql> Select Constraint_name,constraint_type,table_name,status from user_constraints where table_name= ' TEST '; Constraint_name CO table_name STATUS------------------------------------------------PK_TEST_IDP TEST ENABLEDSQL&G T Select Index_name,uniqueness,table_name,status from user_indexes where table_name= ' TEST '; index_nameuniqueness TABLE_ NAME STATUS----------------------------------------------------------------ind_test_idnonunique TEST unusable

As can be seen, closing the index does not affect the state of the corresponding PRIMARY KEY constraint. But is it not possible to insert data as in the previous test?

sql> INSERT INTO test values (1, ' a '); Insert to test values (1, ' a ') *error at line 1:ora-01502:index ' U1. ind_test_id ' or partition of such index is in unusable state

Sure enough, when the index on which the primary key constraint depends is not available, the data is inserted with an error (deleting the data, and an update to the primary key column is also an error), and when you try to delete the index, an error is made, and the index is not allowed to be deleted. This is the same as the previous test.

Sql> Drop Index Ind_test_id;drop index ind_test_id *error at line 1:ora-02429:cannot Drop index used for ENF Orcement of Unique/primary Key

Iii. when creating a primary key constraint, when an index already exists on the corresponding primary key column

What if an index on the ID column already exists in the table test, and then I create the primary KEY constraint on the ID column?

As before, we reset the test environment first.

sql> drop table test purge; Table dropped. Sql> CREATE TABLE Test (ID number,c1 varchar2 (8)); Table created.

We then create an index on the ID column first.

Sql> CREATE index ind_test_id on test (ID); index created.

Next, we create a PRIMARY KEY constraint on the ID column.

Sql> ALTER TABLE TEST add constraint pk_test_id primary key (ID); Table altered.

To view constraints and indexes:

Sql> Select Constraint_name,constraint_type,table_name,status from user_constraints where table_name= ' TEST '; Constraint_name CO table_name STATUS------------------------------------------------PK_TEST_IDP TEST ENABLEDSQL&G T Select Index_name,uniqueness,table_name,status from user_indexes where table_name= ' TEST '; index_nameuniqueness TABLE_ NAME STATUS----------------------------------------------------------------ind_test_idnonunique TEST VALID

As we can see, this time when creating a primary key constraint, an index that already exists on the primary key column is automatically selected and does not, as in test one, create a unique index with the same name as the primary key.

Do we continue to test whether the close or delete constraint will affect the index?

sql> ALTER TABLE TEST disable constraint pk_test_id; Table altered. Sql> Select Constraint_name,constraint_type,table_name,status from user_constraints where table_name= ' TEST '; Constraint_name CO table_name STATUS------------------------------------------------PK_TEST_IDP TEST disabledsql& Gt Select Index_name,uniqueness,table_name,status from user_indexes where table_name= ' TEST '; index_nameuniqueness TABLE_ NAME STATUS----------------------------------------------------------------ind_test_idnonunique TEST VALID

The discovery index is not affected.

Continue testing the effect of closing or deleting an index on a primary KEY constraint.

sql> alter table test enable constraint pk_test_id; Table altered. Sql>  alter index ind_test_id unusable;index altered. Sql>  select constraint_name,constraint_type,table_name,status from user_ Constraints where table_name= ' TEST '; Constraint_name co table_name   status--------------- -- ---------------  ----------------Pk_test_idp  test    enabledsql> select index _name,uniqueness,table_name,status from user_indexes where table_name= ' TEST '; INDEX_ Nameuniqueness   table_name   status--------------- ------------------  --------------- ----------------ind_test_idnonunique   test     Unusablesql>  insert into test values (1, ' A ');  insert into test  values (1, ' A ') *error at line 1:ORA-01502: index  ' U1. ind_test_id '  or partition of such index is in unusable statesql > drop index ind_test_id;drop index ind_test_id            *ERROR at line 1:ORA-02429: cannot drop  Index used for enforcement of unique/primary key

As with previous tests, closing the index on which the primary key constraint depends does not affect the state of the primary KEY constraint, but modifies the data (including additions and deletions, where modifications are specifically made to the primary key column) are not allowed. Similarly, dropping an index is not allowed.

At this point, we summarize the above three major tests:

1. When creating a PRIMARY key constraint, an automatically generated unique index with the same name:

When you close or delete a primary key constraint, the corresponding index is deleted.

2. When creating a PRIMARY key constraint, use a non-auto-created index (specify the index on the primary key column when creating the index or creating a PRIMARY KEY constraint in the SQL that created the primary KEY constraint). ）：

When you close or delete a primary key constraint, the corresponding index is not deleted and the index state is not affected.

3. In either case, closing the index on which the primary key constraint is dependent can result in data insertions, deletions, and errors on the primary key column update operation.

4. In either case, deleting the index on which the primary key constraint is dependent is not allowed.

Attention:

In addition, there is a special case. That is, when we use the second method, when creating a primary key constraint, using the Using index clause to create a unique index , such as:

ALTER TABLE TEST ADD constraint pk_test_id primary key (ID) using index (create unique index ind_test_id_uni on test (ID));

If I close or delete the primary KEY constraint at this point, the index is automatically deleted. When we restore the PRIMARY KEY constraint, the database automatically creates a unique index with the same name as the primary key. In addition, when we close a manually created unique index or delete it, the situation is the same as stated above.

This article is from the "Big Face Cat" blog, please be sure to keep this source http://bfc99.blog.51cto.com/265386/1763403

Relationship and interaction between primary key and primary key index