Naming Conventions – Primay and Foreign Keys
Hank writes in with a scenario revolving around system assigned key names.
In Microsoft SQL Server you can declare constraints without providing names. In these situations, the system assigns a name on your behalf, usually part of the key looking like a GUID. There is little meaning or value in the name. The value is in enforcing the purpose of the constraint.
Hank says:
…I have duplicated foreign Keys.. Yes I inherited them.. I can delete the duplicates no problem there !!
Additionally, the name the system uses when the foreign key is assigned is not really usable, so I have decided to change the FKeyName.
So I looked at the SQL-92 standard and it did not come off the text on how to resolve the name so I have decided to use the following.
FK_FKTableName_FKColumnName_PrimaryTableName_PrimaryColumnName, which I believe this will make it unique enough and allow for a cleaner printout on a model to boot.
Sooo, if this was your problem and you wanted to make sense of 700 foreign keys, how would you approach the problem..
I have found the following naming conventions to be useful.
Primary keys I name with the prefix PK_ followed by the table name.
Alternate keys (unique indexes that may be used as foreign keys) I name with AK_ followed by the table name.
Using these two conventions I don’t have to name any columns. That may be derived from the meta-data contained in the INFORMATION_SCHEMA views.
Foreign key constraintss are a little different. In this case I name the constraint FK_[ChildTableName}_[ParentTableName]. Most often I don’t have a foreign key constraint based on an -alternate key. Again, if that were the case, you could obtain that information from one of the INFORMATION_SCHEMA views.
For me, these names have proven useful. Including column names may not be possible because I tend to use meaningful column names, which may cause the key to be too long.
How I would approach Hanks problem is that I would find the unique set of foreign keys currently existing regardless of name, and create a script using the naming convention I presented above. I would create a script dropping all the old constraints, and then create the new set of constraints.
If the database is too big, and this takes too long, then you might consider removing all duplicates. Then you could create a script that that would use SP_RENAME to rename all the existing foreign keys using your new naming convention.
Don’t ask me how to do it. I don’t know offhand. I know I can get the information necessary from INFORMATION_SCHEMA views and/or system views.
Next we’ll talk about naming conventions for code. If you have some suggestions you’d like to contribute send them to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008
Written by Townsend Security
Simplify encryption and key management on … (read more)