Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics

Dashboard
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‭ 2 months 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‭ 2 months 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‭ 2 months ago

Sign up to answer this question »