Oracle與MySQL刪除字段時對索引和約束的處理

不知道有多少人清楚的知道,在Oracle中,如果一個復合索引,假定索引(a,b,c)三個字段,刪除瞭(包括unused)其中一個字段,Oracle會怎麼處理這個索引。同樣,如果是約束,Oracle又怎麼處理?



用Oracle為例子,我又拿mysql做瞭一個對比,看看mysql是怎麼處理這個問題的。我這裡不討論誰好誰差,隻是希望大傢知道其中的差別與細節而已。



我們先看Oracle的例子,我們創建一個表,然後在上面創建一個約束,創建一個索引:



SQL10G>createtabletest(aint,bint,cint);


Tablecreated.


SQL10G>altertabletestaddconstraintpk_testprimarykey(a,b);


Tablealtered.


SQL10G>createindexind_testontest(b,c);


Indexcreated.



然後,我們檢查剛才創建的約束與索引



  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.constraint_type!=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*orderbyindex_name,column_position


INDEX_NAMECOLUMN_NAMECOLUMN_POSITIONSTATUS


————————————————-


IND_TESTB1VALID


IND_TESTC2VALID



現在,我們先刪除索引上的字段,其實並沒有物理刪除,隻是設置為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



發現瞭什麼,索引也刪除瞭。那我們再刪除約束上的字段呢?



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.constraint_type!=C


5andt.table_name=TEST


6orderbyconstraint_name,position;


norowsselected



我們可以看到,正常的刪除會報一個錯誤,如果我們指定瞭cascade,將會把對應的約束也刪除。



我們看完瞭Oracle的處理過程,再看看mysql是這麼處理刪除索引上字段這個事情的



mysql>createtabletest(aint,bint,cint);


QueryOK,0rowsaffected(0.72sec)


mysql>altertabletestaddprimarykey(a,b);


QueryOK,0rowsaffected(0.27sec)


Records:0Duplicates:0Warnings:0


mysql>createindexind_testontest(b,c);


QueryOK,0rowsaffected(0.32sec)


Records:0Duplicates:0Warnings:0



我們執行同樣的操作,先刪除復合索引中的一個字段,然後刪除約束中的一個字段。



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)



可以看到,mysql的處理方式是有差別的,mysql僅僅是把字段從索引中拿掉,而不是刪除該索引。



本文的意思,就是想提醒大傢,平常在做columns刪除的時候,包括unused,一定要小心,是否有復合索引包含瞭該字段,否則,一不小心把索引刪除瞭,可能將引發大的錯誤。

You May Also Like