Why would an unique index get moved to the primary key after the underlying column is dropped?
So I had a table with a primary key and a bunch of different columns. Columns A, B, and C were all unsigned ints (like the primary key column) and each column had a unique constraint
I dropped the A, B, and C columns, and then I got a warning that there were now duplicate unique constraints. When I checked the table structure I saw that unique indexes had been moved to the primary key column.
My assumption was that dropping a column would also drop its index, why would the index be moved to the primary key?
1 answer
I can't say that I fully understood what you did, but I think I got the explanation:
- removed 1+ columns that were part of the index. The index will be updated to not include that column.
If columns are dropped from a table, the columns are also removed from any index of which they are apart. If all columns that make up an index are dropped, the index is dropped as well.
- all secondary indexes include the PK columns. Not really sure, but dropping all the non-PK columns from the indexes you can get to your strange situation when the indexes are basically the same as the PK.
2 comments
Each column had a unique key, not a unique key on the combination
@Charlie Brumbaugh Yes, that means that the index contains that column + columns in the PK. That would explain the second point which actually seems to be the only point: all indexes will only cover the column in the PK.
1 comment
It'd probably be more helpful if you include the SQL you used to create the table, its columns, and then the SQL used to drop the columns, along with the version of MySQL you're using, and so on. It'd also help in clearing up ambiguities. — ghost-in-the-zsh 4 months ago