Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved in your SSC briefcase from the favorites tab.
Download now (direct download link)

Fix Foreign Key Constraint Naming

By Michael Søndergaard,

The stored procedure dbautils.spFixForeignKeyNaming was written, because a lot of the databases I had inherited never had any consistency in their foreign key naming. Most of them had default names from the database table designer, some was named with almost random names, and others were based on some sort of naming convention, but were never followed through.

I like it, then the foreign key names, can help me to identify the tables and columns involved, without having to look at the meta data.

I accept that there are many different naming conventions, which people uses. That is why, I tried to create a very flexible way of enforcing naming conventions, and having the ability to support most of these different types of conventions.

The way to design a naming convention is with the use of the place holder tokens. Place holder tokens represent variables, which are substituted, with values from the database objects. Following place holder tokens are supported for foreign key naming conventions

Placeholder token Description
%PARENT_SCHEMA_NAME% Returns the schema name for the parent table, for which the foreign key belongs to
%PARENT_TABLE_NAME% Returns the table name without a schema part for the parent table, for which the foreign key belongs to
%PARENT_COLUMNS% Returns a list of parent columns for the foreign key constraint. The separator used between foreign key columns are determined by the @ColumnsSeparator
%REFERENCED_SCHEMA_NAME% Returns the schema name for the referenced table, for which the foreign key points to
%REFERENCED_TABLE_NAME% Returns the table name without a schema part for the referenced table, for which the foreign key points to
%REFERENCED_COLUMNS% Returns a list of referenced columns for the foreign key constraint. The separator used between foreign key columns are determined by the @ColumnsSeparator
%COLUMNS_SEPARATOR% Returns the @ColumnsSeparator value
%ENABLED_STATE% Returns the value corresponding to value in the @EnabledStates parameter. The value returned depends on, if the foreign key constraint are enabled or disabled
%REPLICATION_STATE% Returns the value corresponding to value in the @ReplicationStates parameter. The value returned depends on, if the foreign key constraint are enforce in replication scenarios
%TRUSTED_STATE% Returns the value corresponding to value in the @TrustedStates parameter. The value returned depends on, if the foreign key constraint are trusted by sql server or not
%DEL_REF_ACTION% Returns the value corresponding to value in the @DeleteRefStates parameter. The value returned depends on, the foreign key constraint delete referential action
%UPD_REF_ACTION% Returns the value corresponding to value in the @UpdateRefStates parameter. The value returned depends on, the foreign key constraint update referential action

How to use the stored procedure

I recommend using named parameters, instead of specifying the whole parameter list. I made all parameters optional, with default values. So it is actually possible to use, without specifying any parameters. Running the stored procedure is safe, because it will not change the database, it will only generating a report or rename statements. Only if the parameter @PerformUpdate >= 1 will it change the database directly.

I will demonstrate how to use the stored procedure by some examples, and by using the AdventureWorks database. If you want to try it yourself, you can download the AdventureWorks database from
http://msftdbprodsamples.codeplex.com/Wikipage.

I will go through all of the parameters, available for this stored procedure. The parameters are also documented inside the header section of the stored procedure code. You could also look at the test cases for seeing their functionality. For easier usage I have also tried to align the functionality, so that parameters work the same way, across all my procedure.

Example 1 - Executing with default parameters only

Running the procedure as below, would generate a series of sp_rename statements, for renaming all foreign keys. It will only rename those, which doesn't comply with the default naming convention.


EXECUTE dbautils.spFixForeignKeyNaming


The procedure generates the following result (truncated, and selected samples picked out)

EXECUTE sp_rename '[Person].[Password].[FK_Password_Person_BusinessEntityID]','FK_Password_Person', 'object'
EXECUTE sp_rename '[Person].[Person].[FK_Person_BusinessEntity_BusinessEntityID]','FK_Person_BusinessEntity', 'object'
EXECUTE sp_rename '[Person].[PersonPhone].[FK_PersonPhone_Person_BusinessEntityID]','FK_PersonPhone_Person', 'object'

Example 2 - Custom naming convention for foreign keys.

Let's say, I wanted to see, how all the foreign keys in the Adventure Works database, would look like, with my own custom naming convention. For this purpose the report mode is easier to read, so I need to set the parameter @ReportMode = 1.

I use my preferred naming convention for this example. It will tell me the name of the tables used for the foreign key, and also all the parent foreign key columns.


EXECUTE dbautils.spFixForeignKeyNaming
@NamingConvention= 'FK_%PARENT_SCHEMA_NAME%_%PARENT_TABLE_NAME%__%REFERENCED_SCHEMA_NAME%_%REFERENCED_TABLE_NAME%~%PARENT_COLUMNS%',
@UniquifyNames = 0,
@ReportMode = 1

The execution of the procedure generates the following result (truncated and selected samples picked out)

SchemaName TableName OldName NewName
Sales Customer FK_Customer_Person_PersonID FK_Sales_Customer__Person_Person~PersonID
Sales Customer FK_Customer_Store_StoreID FK_Sales_Customer__Sales_Store~StoreID
Production WorkOrder FK_WorkOrder_Product_ProductID FK_Production_WorkOrder__Production_Product~ProductID
Production ProductReview FK_ProductReview_Product_ProductID FK_Production_ProductReview__Production_Product~ProductID
Purchasing Vendor FK_Vendor_BusinessEntity_BusinessEntityID FK_Production_ProductReview__Production_Product~ProductID

Now it is easier to spot, which schema and tables are involved and a list of columns. In the databases I work with, the same table name can be used in different schemas. I also like it when it is easy to spot, there the referenced table and the column list begins.

Example 3 - Solving duplicate generated names.

The standard behavior for the stored procedure is to make sure, that no generated names are the same. This is done is by appending an incrementing number to the name, but only for duplicate names. This behavior was suppressed in one of the previous examples, with the use of the parameter @UniquifyNames = 0.

There are limitations to the stored procedure; it will only uniquify those foreign key names, which are returned as duplicates. It will not resolve name collisions with existing foreign key names, nor any dependency rename ordering, which may be present.

Let's see the uniquify functionality in action.

EXECUTE dbautils.spFixForeignKeyNaming
@ReportMode = 1

The procedure generates the following result (truncated, and selected samples picked out)

SchemaName TableName OldName NewName
Production BillOfMaterials FK_BillOfMaterials_Product_ProductAssemblyID FK_Production_BillOfMaterials__Production_Product
Production BillOfMaterials FK_BillOfMaterials_Product_ComponentID FK_Production_BillOfMaterials__Production_Product2
Production Product FK_Product_UnitMeasure_SizeUnitMeasureCode FK_Production_Product__Production_UnitMeasure
Production Product FK_Product_UnitMeasure_WeightUnitMeasureCode FK_Production_Product__Production_UnitMeasure2

Instead of the uniquifier functionality, it is also possible to fix the problem, by solving it manually. The only reason the problem exist in our example, was because we didn’t include the parent columns, another situation could be that same tables were used in different schema’s, the default convention doesn’t include schema, so it would also lead to duplicate names.

Example 4 - Solving long names with oversized mode - truncating & skipping

It is possible to limit the maximum length of a generated foreign key name, by setting a max length. All names that are larger than the specified @MaxNameLength parameter, or the SQL Server limit of 128 characters, will be truncated. Uniquifiers don't count towards the limit. Let's limit the names to a maximum of 40 characters.

EXECUTE dbautils.spFixForeignKeyNaming
@NamingConvention= 'FK_%PARENT_SCHEMA_NAME%_%PARENT_TABLE_NAME%__%REFERENCED_SCHEMA_NAME%_%REFERENCED_TABLE_NAME%',
@MaxNameLength = 40,
@OversizedMode = 'T',
@ReportMode = 1

The procedure generates the following result (truncated, and selected samples picked out).

SchemaName TableName OldName NewName
Purchasing PurchaseOrderDetail FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID FK_PurchaseOrderDetail_PurchaseOrderHead
Sales SalesOrderHeaderSalesReason FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID FK_SalesOrderHeaderSalesReason_SalesOrde

This shows that foreign keys, had their new names truncated to a maximum of 40 characters.

Instead of truncating the foreign keys, setting the @OversizedMode = ‘S’, would have skipped the same foreign keys, and left them for manual renaming.

Example 5 - Solving long names by limiting the number parent or referenced columns

Truncating foreign key names at a fixed length, can leave some names cut off at inconvenient places. It is possible to set a limit for how many parent and referenced columns, which should be a part of the foreign key name. This is done by the parameters @MaxParentColumns and @MaxReferencedColumns.

EXECUTE dbautils.spFixForeignKeyNaming
@NamingConvention= 'FK_%PARENT_TABLE_NAME%__%REFERENCED_TABLE_NAME%~%PARENT_COLUMNS%~%REFERENCED_COLUMNS%',
@MaxParentColumns = 1,
@MaxReferencedColumns = 1,
@ReportMode = 1

The procedure generates the following result (truncated, and selected samples picked out).

SchemaName TableName OldName NewName
Sales SalesOrderDetail FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID FK_SalesOrderDetail__SpecialOfferProduct~SpecialOfferID~SpecialOfferID

Example 6 – Solving long names with object aliases

The stored procedure can automatically replace schema, table, parent or referenced column name, with an object alias instead.

It works by using a mapping table and a synonym. The mapping table requires a certain structure, but may be named whatever you like. This is there the synonym comes in. The stored procedure requires the name to be called dbautils.AliasRulesSynonym, but it may point to a table in an external database. This is useful if you like to have a central object alias repository. The synonym give the flexibility, that you don't need to change the code of the stored procedure, you only need to change what the synonym points to.

The parameter @UseAliases is a char mask. By setting the correct char mask, you have total control of what types of aliasing you want to enable.

'C' = Column aliasing

'T' = Table aliasing

'S' = Schema aliasing

Shown below is an example of an alias rules table. Some of them may be a little artificially, but should make it easier to understand.

% mean a wildcard, the rule applies for all objects in that database area. It is only allowed to use either an exact object name, or %. Partial names like Prod% are not allowed.

More than one rule can apply for the same object, but the more specific rules, takes precedence over the more generic ones. A more thorough explanation of this, can be found in the documentation for the table value function dbautils.fnGetObjectAliases.

Please also note the importance of the empty table and column names, as they influence that type of alias rule it is.

Shown below is an example of an alias rules table. Some of them may be a little artificially, but should make it easier to understand.

% mean a wild-card, the rule applies for all objects in that database area. It is only allowed to use either an exact object name, or %. Partial names like Prod% are not allowed.

More than one rule can apply for the same object, but the more specific rules, takes precedence over the more generic ones. A more thorough explanation of this can be found in the documentation for the table value function dbautils.fnGetObjectAliases.

Please also note the importance's of the empty table and column names, as they influence that type of alias rule it is.

DatabaseName SchemaName TableName ColumnName AliasName Alias Rule Type
% Production     Prod Schema alias rule
AdventureWorks Research     Rsrch Schema alias rule
% % Product   Prod Table alias rule
AdventureWorks % BillOfMaterials   BOM Table alias rule
% % % Name NM Column alias rule
AdventureWorks % % Name Nam Column alias rule
AdventureWorks % Product Name PNam Column alias rule
AdventureWorks Research Product Name PNm Column alias rule
AdventureWorks Production % ProductId PID Column alias rule
AdventureWorks Production BillOfMaterials ProductAssemblyID ProAssID Column alias rule
AdventureWorks Production BillOfMaterials ComponentID CompID Column alias rule
% % % StartDate StDate Column alias rule
% Person % StateProvinceID StateProvId Column alias rule
% % % DatabaseLogID DBLogId Column alias rule
% % % Comments Cmnts Column alias rule
% % % ReviewerName RevNam Column alias rule
EXECUTE dbautils.spFixForeignKeyNaming 
  @NamingConvention= 'FK_%PARENT_SCHEMA_NAME%_%PARENT_TABLE_NAME%__%REFERENCED_SCHEMA_NAME%_%REFERENCED_TABLE_NAME%~%PARENT_COLUMNS%~%REFERENCED_COLUMNS%', 
  @UseAliases = 'STC', 
  @ReportMode = 1


The procedure generates the following result (truncated, and selected samples picked out).

SchemaName TableName OldName NewName
Production Product FK_Product_ProductModel_ProductModelID FK_Prod_Prod__Prod_ProductModel~ProductModelID~ProductModelID
Production BillOfMaterials FK_BillOfMaterials_Product_ComponentID FK_Prod_BOM__Prod_Prod~CompID~CompID
Purchasing PurchaseOrderDetail FK_PurchaseOrderDetail_Product_ProductID FK_Purchasing_PurchaseOrderDetail__Prod_Prod~ProductID~ProductID
Production TransactionHistory FK_TransactionHistory_Product_ProductID FK_Prod_TransactionHistory__Prod_Prod~PID~PID
Production BillOfMaterials FK_BillOfMaterials_Product_ProductAssemblyID FK_Prod_BOM__Prod_Prod~ProAssID~ProAssID
Production WorkOrder FK_WorkOrder_Product_ProductID FK_Prod_WorkOrder__Prod_Prod~PID~PID

The @UseAliases parameter may be combine with the other parameters @MaxNameLength, @MaxParentColumns, @MaxReferencedColumns and @OversizedMode. All of these options can help shortening the foreign key names.

Example 7 – Renaming subset of foreign keys

Sometimes renaming all noncompliant foreign keys in one batch, can be a rather large change, so I have made it possible to easily filter a subset of tables. This is controlled by the parameter @FilterExpression, and is basically a like expression in disguise. The filter is applied on the parent schema and table

Here are some examples for the most common expressions and what they mean.

Filter expression Meaning
% All foreign keys for all tables in all schemas
%.% All foreign keys for all tables in all schemas
%.Product All foreign keys with parent tables named product in all schemas
Production.% All foreign keys with parent tables in schema Production
Prod%.% All foreign keys with parent tables in schemas beginning with the name Prod
%,-Production.% All foreign keys with parent tables in all schemas, except parent tables in the Production schema
Archive.%,-Archive.%199[0-9] All foreign keys with parent tables in the Archive schema, except parent tables in schema Archive, there the table name ends with a year between 1990 and 1999

Here is an example of finding foreign key names in the Production schema, which doesn’t comply with the standard foreign convention.

EXECUTE dbautils.spFixForeignKeyNaming
@FilterExpression = 'Production.%',
@ReportMode = 1

Example 8 – Renaming foreign keys with forced case sensitivity

In databases which are using a case insensitive collection, the procedure won’t report nor create rename statements for foreign keys, which complies to the naming convention, but differs in casing.

If you want your foreign keys to match in casing too, when you can force it, by setting the @ForceCaseSensitivity = 1. This will compare the old name with the new name by using the collation Latin1_General_BIN. If you want use another collation for case sensitive comparison; when you will have to adjust the stored procedure code. I haven’t found a better solution for accomplishing this, without the use of a case sensitive collation.

EXECUTE dbautils.spFixForeignKeyNaming
@FilterExpression = 'Production.%',
@ForceCaseSensitivity = 1,
@ReportMode = 1

Final stuff

I have also created similar stored procedures for applying custom naming convention for column check constrains, foreign key constrains and indexes. These procedures will also be released to SQL Server Central. You can find the latest versions, updated documentation, test case scripts and other SQL goodies at sql.soendergaard.info

Total article views: 1248 | Views in the last 30 days: 11
 
Related Articles
SCRIPT

Find Foreign Keys

This script lists all foreign keys with parent and child table information

FORUM

Trace Parent Table from Foreign Key

Trying to find parent table

FORUM

Multiple columns and foreign key relationships

A single column in a child table ref. multiple columns in a parent table

FORUM

How to get schemaname, tablename, identity column, foreign key constraints

How to get schemaname, tablename, identity column, foreign key constraints

SCRIPT

Find Missing Foreign Keys

Find missing foreign keys in your database schema based on naming conventions.

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones