Menu
With one of my MySQL tables, I dropped column col1 before I drop it from a unique index (col0, col1, col2, col3) that contains it.
Is it automatically taken care of by MySQL? It seems the unique index that was previously (col0, col1, col2, col3) was automatically changed to (col0, col2, col3) after I deleted the column col1.
Is it going to be a problem or do I have to drop the unique index and re-create it as (col0, col2, col3)?
735
If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well.
Yes, it does. However, if you have foreign key constraints such as RESTRICT that ensure referential integrity with other tables, you'll want to drop those keys prior to dropping or truncating a table.
So the order of columns in a multi-column index definitely matters. One type of query may need a certain column order for the index. If you have several types of queries, you might need several indexes to help them, with columns in different orders.
Dropping an index does not cause any other objects to be dropped but might cause some packages to be invalidated. A primary key or unique key index cannot be explicitly dropped.
According to the MySQL 5.1 Reference Manual:
If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well. If you use CHANGE or MODIFY to shorten a column for which an index exists on the column, and the resulting column length is less than the index length, MySQL shortens the index automatically.
161
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
