Tuesday, November 25, 2008

should you name foreign key constraints in MySQL?

In MySQL, if you don't name your foreign key constraints, the database generates a name for them automatically. Foreign key constraint names must be globally unique within the database, so unless you have a reason to name them, it is probably more hassle than it is worth.

I happen to have a reason to want to name them, and our setup is probably not that uncommon, so others may discover that it is advantageous to name them, too. We have three different databases where I work - one for development ('dev'), a staging database for testing each iteration, and patches ('staging'), and, of course, our live database that the site runs on ('live'). Additionally, each developer has a local database on their personal machines, to develop against.

For each iteration, we create a single schema migration script as we are developing. It will likely get run in pieces on the dev database, and there may be multiple revisions to a table as the iteration develops. Usually the script is very final by the time it is run against staging, but there is always the possibility of additional changes late in the game.

So where foreign key constraint names come into play is when you want an alter statement that can be run against all the different databases without changing. If you leave the naming up to the database, they are typically named in a sequential fashion (first foreign key constraint will probably have a '_1' at the end, the next will have '_2', etc.).

The problem is that if you create and drop foreign keys in different orders on different databases, the names won't match up. The foreign key named 'blah_1' on dev might be on a different column than the one with the same name on staging. You have to alter them by name, so there is no way to have a single script that will run correctly on all of the databases.

No comments:

Post a Comment