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.