This post is not very cfml specific, but I am using this a lot with cfmigrations, so it might be worthwhile sharing. Recently I was doing some major restructuring of a database, and most of the time I try to script this using cfmigrations or commandbox-migrations. Recently I had to drop some tables and thought this would be easy with cfmigrations. So I fired up commandbox, and executed
migrate create dropOldTables
which creates a cfc named yyyy-mm-dd_dropOldtables.cfc in my resources/database/migrations
map. If you didn’t use commandbox-migrations before, you have to install and init first, as described here. The migration command should be trivial, something like:
function up( schema, query ) {
schema.drop("ipnumbers");
}
Easy. Just run a migrate up and my table has gone . Exactly what I want.
At least, that was the plan. Probably there’s some table who is still referencing this ipnumbers table, but which one of my 100+ other tables?
That’s where I found a very useful but overwhelming post with 11 Ways to Return Foreign Keys in a SQL Server Database. That should be enough. There are many ways to list foreign keys, and when googling you probably find something useful enough, but this post lists many of them. I will only explain two of these methods, because that’s all I need right now. One of the most useful methods is this one (Example 8 in the original post):
SELECT
OBJECT_NAME(constraint_object_id) AS 'Foreign Key',
OBJECT_NAME(parent_object_id) AS 'Table',
OBJECT_NAME(referenced_object_id) AS 'Referenced Table'
FROM sys.foreign_key_columns
-- optional filter on REFERENCED table
where OBJECT_NAME(referenced_object_id) = 'ipnumbers'
Without the filter part it will list all foreign key names, including tables and referenced tables in my database. With this filter it just shows me the foreign keys in other tables which prevent me from dropping my tables, e.g
As you can see here I still had another (obsolete) table which was referring to the ipnumbers table. So this leaves me two options: dropping the foreign key constraint on the other table like:
function up( schema, query ) {
schema.alter( "networkInterfaces", function( table ) {
table.dropconstraint("FK1149330A3F433A4");
} );
schema.drop("ipnumbers");
.....
or, even easier in my case, just dropping the networkInterfaces table as well. But at least, I have some more information about my foreign keys now.
This brings me to the second useful Example from ’11 ways to return a foreign key’ and this is Example 1. The names of my foreign keys are quite horrible: FK1149330A3F433A4
is not really informative. I am not sure where this went wrong lang ago, but probably there was a lot of autocreating involved, both by CFORM and the visual MS SQL visual database tools. Nowadays I modify and inspect my database tables, just to notice the naming is not what I wanted. Look at this for example:
In cases like this one, it is good to know you can view all information on foreign keys of a table just by executing
EXEC sp_fkeys @fktable_name = customers;
which will give me all necessary information on the foreign keys in my table:
Of course it is way better to create a decent naming schema for my foreign keys, but at least I have an overview of what my cryptic names mean. Armed with this information it is trivial to change the names of these foreign keys in cfmigrations, so I can rename my foreign key on the documentTypeID field in this example to something more meaningful (fk_tablename_ForeignKeyColumName):
schema.alter( "documents", function( table ) {
table.renameConstraint(
"FK600E7C55F34FE6C4",
"fk_documents_documentTypeID"
);
} );
If I ever want to create new foreign keys with qb SchemaBuilder I have two options:
schema.create( "users", function( table ) {
// create field and foreign key in same statement
table.unsignedInteger( "countryId" ).references( "id" ).onTable( "countries" );
// OR create field first, followed by a Named FK
table.unsignedInteger( "countryId" );
table.foreignKey( "countryId", "myCountry_FK" ).references( "id" ).onTable( "countries" );
} );
If you use the references()
construct the Foreign key name will be automatically created in this format: fk_tablename_ForeignKeyColumName. If you want full control on the names of your foreign keys, you can use the foreignKey(
) method which has an optional second parameter for the key name.
So from now on, I have no reason to use these cryptic key names anymore!
Leave a Reply