A wise man once said, "when it comes to databases don't be exceptional; be normal". For a project I'm working on data normalisation was part of the process to make reporting more accurate and some future work much easier to complete. This particular database had two tables, A and B, with a value in A that obviously references B. The issue was that A didn't have an ID for a reference, A had a duplicated value of what's stored in B - all managed by the application. One key reason why we didn't like this setup was that updates in B won't cascade to A.
In this case we want to modify A so that it'll store the IDs of the values it wants in B. Once that's in place we can modify MySQL so that it knows that one of A's foreign keys is the ID of B.
We're updating A but also joining B beforehand because we want to get a value
from that table. The keyword
USING is used to join a table when they have
the same column name. If that wasn't the case, you could use the more common
ON A.some_column = B.another_column.
At this point we replaced the hardcoded values to IDs. We can now go ahead and rename the column and make it a foreign key.
-- Name change
And that's a wrap, a better database that leads to a better world. Happy normalising everyone!