Shulou(Shulou.com)05/31 Report--

This article focuses on "Oracle and MySQL delete fields when the treatment of indexes and constraints", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to deal with indexes and constraints when deleting fields in Oracle and MySQL.

I don't know how many people know clearly what Oracle would do with a composite index if it had three fields and deleted (including unused) one of its three fields in Oracle. Similarly, what does Oracle do if it is a constraint?

Using Oracle as an example, I made another comparison to see how mysql deals with this problem. I don't discuss who is good and who is bad here. I just want you to know the differences and details.

Let's look at the example first, let's create a table, then create a constraint on it, create an index:

SQL10G > createtabletest (aint,bint,cint)

Tablecreated.

SQL10G > altertabletestaddconstraintpk_testprimarykey (afort b)

Tablealtered.

SQL10G > createindexind_testontest (bPermec)

Indexcreated.

Then we examine the constraints and indexes that we just created

SQL10G > selectt.constraint_name,c.constraint_type,t.column_name,t.position,c.status,c.validated

2fromuser_cons_columnst,user_constraintsc

3wherec.constraint_name=t.constraint_name

4andc. Intact typewriting C

5andt.table_name=TEST

6orderbyconstraint_name,position

CONSTRAINT_NAMECCOLUMN_NAMEPOSITIONSTATUSVALIDATED

PK_TESTPA1ENABLEDVALIDATED

PK_TESTPB2ENABLEDVALIDATED

SQL10G > selectt.index_name,t.column_name,t.column_position,i.status

2fromuser_ind_columnst,user_indexesi

3wheret.index_name=i.index_name

4andt.table_name=TEST

5 columnkeeper position

INDEX_NAMECOLUMN_NAMECOLUMN_POSITIONSTATUS

IND_TESTB1VALID

IND_TESTC2VALID

Now, let's delete the fields on the index first. In fact, there is no physical deletion, just set to unused:

SQL10G > ALTERTABLEtestSETUNUSED (c)

Tablealtered.

SQL10G > selectt.index_name,t.column_name,t.column_position,i.status

2fromuser_ind_columnst,user_indexesi

3wheret.index_name=i.index_name

4andt.table_name=TEST

5orderbyindex_name,column_position

Norowsselected

What was found, the index was deleted. What if we delete the fields on the constraint?

SQL10G > ALTERTABLEtestSETUNUSED (b)

ALTERTABLEtestSETUNUSED (b)

*

ERRORatline1:

ORA-12991:columnisreferencedinamulti-columnconstraint

SQL10G > ALTERTABLEtestSETUNUSED (b) CASCADECONSTRAINTS

Tablealtered.

SQL10G > selectt.constraint_name,c.constraint_type,t.column_name,t.position,c.status,c.validated

2fromuser_cons_columnst,user_constraintsc

3wherec.constraint_name=t.constraint_name

4andc. Intact typewriting C

5andt.table_name=TEST

6orderbyconstraint_name,position

Norowsselected

We can see that a normal deletion will report an error, and if we specify cascade, the corresponding constraint will also be deleted.

After looking at the process of Oracle, let's take a look at how mysql handles deleting fields on the index.

Mysql > createtabletest (aint,bint,cint)

QueryOK,0rowsaffected (0.72sec)

Mysql > altertabletestaddprimarykey (afort b)

QueryOK,0rowsaffected (0.27sec)

Records:0Duplicates:0Warnings:0

Mysql > createindexind_testontest (bPermec)

QueryOK,0rowsaffected (0.32sec)

Records:0Duplicates:0Warnings:0

We do the same by deleting a field in the composite index and then deleting a field in the constraint.

Mysql > altertabletestdropc

QueryOK,0rowsaffected (0.58sec)

Records:0Duplicates:0Warnings:0

Mysql > showindexfromtest

+-+

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | |

+-+

| | test | 0 | PRIMARY | 1 | a | A |

| | test | 0 | PRIMARY | 2 | b | A |

| | test | 1 | ind_test | 1 | b | A |

+-+

3rowsinset (0.06sec)

Mysql > altertabletestdropb

QueryOK,0rowsaffected (0.28sec)

Records:0Duplicates:0Warnings:0

Mysql > showindexfromtest

+-+

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | |

+-+

| | test | 0 | PRIMARY | 1 | a | A |

+-+

1rowinset (0.03sec)

As you can see, mysql is handled differently, and mysql simply removes the field from the index, not deletes the index.

The meaning of this article is to remind you that when you usually do columns deletions, including unused, you must be careful whether there is a composite index containing this field, otherwise, accidentally delete the index, may cause a big error.

At this point, I believe you have a deeper understanding of "Oracle and MySQL delete fields when the treatment of indexes and constraints", might as well come to the actual operation! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.