Mysql – Converting a composite primary key to composite unique, and adding an auto-incremented PK column

innodbMySQLprimary-keytableunique-constraint

Background

I have taken over responsibility for a table that, in the long run, will grow monolithic. The table is a very simple link table with 2 columns, call them col1 and col2, both being foreign keys to their respective tables, and also composite primary keys.

Before this table grows any further, I want to add an id column (NOT NULL AUTO-INCREMENT PRIMARY KEY), and instead let col1, col2 have a composite unique constraint.

The reasons for me wanting to do this lies in the business logic.

Question

Is there anything I should be particularly aware of before doing this? I'm going back and forth as to which order I should do it in.

Any insight appreciated!

Assumptions

The added column will of course increase required storage space. This is not an issue.

Bandwidth and overhead in terms of CRUDing towards the table is also accounted for.

Setup: MySQL / InnoDB

The why

The comments have been deleted, but there were some people asking why I wanted to make this change, and told me that it was a stupid change to make.

I'll answer it to prevent the XY problem (even though I don't think it's necessary in this case), and because the question has remained answerless thus far.

The reason I want to make this change, is because this link table will be linked to, from at least one other table, namely a change log.

Consider the change log table having the columns: id, col1 (FK), col2 (FK), user_id, action, timestamp. Now, if I want to have a changelog, I'm duplicating the entire original link table by virtue of having both col1 and col2 there.

At this point, it's an even trade. Saved the space in the link table, wasted the same amount of space in the change log table.

Now, who's to say I'll never need another table to link to it? That would be a pretty big assumption to make.

Best Answer

That smells a lot like a "many-to-many" relationship table. Is it?

As for linking from a changelog -- space is not the issue; correctness is. A change log should have exactly what existed at the time the log was taken. What if the original table is updated? If the table in question is changed for any reason, I think you do not want the change reflected in the existing changelog entry. (Though, perhaps in a new changelog entry.)

If you would like to discuss your particular case further, please provide SHOW CREATE TABLE and a few queries that use the table.

Here's another tip: It is OK to do the following:

id INT UNSIGNED AUTO_INCREMENT,
...
PRIMARY KEY (...),
INDEX(id)

That keeps the original PK clustered, which might be beneficial, yet still allows for an AI key.

Another tip: Don't say UNIQUE(foo) in place of INDEX(foo) unless there is some reason for doing the uniqueness constraint. (Example: INSERT ... ON DUPLICATE KEY ...)

Further discussion about many-to-many.

Related Question