Notifications
Mark all as read
Q&A

Why would an unique index get moved to the primary key after the underlying column is dropped?

+2
−0

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?

Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

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‭ 20 days ago

1 answer

+3
−0

I can't say that I fully understood what you did, but I think I got the explanation:

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.

Why does this post require moderator attention?
You might want to add some details to your flag.

2 comments

Each column had a unique key, not a unique key on the combination Charlie Brumbaugh‭ 20 days ago

@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. Alexei‭ 20 days ago

Sign up to answer this question »