Foreign Key Names
Today Thomas shares his thoughts on constraint names as well as indexes:
For Foreign keys I use FK_PARENTTABLE_parentcol1_parentcol2_CHILDTABLE_childcolum1_childcolumn2
Currently, we don’t have any keys more complex than 2 columns and we are well under the 128 character limit. Our child column names are usually the same as their parent’s so I’ll probably resort to some kind of shorthand to indicate this when/if I run into the name size limit.
I prefer to have the column names especially for Foreign keys where it’s more difficult to pull that information from the information schema, that way a problem with an FK violation can be more easily understood and handled by our lower level support teams and our dot net developers who aren’t as proficient at SQL. I definitely don’t want to let SQL assign the names since I have over 200 (soon to be 6000+) servers that I want to insure have the same database schema, and making sure they all have the latest FK changes is much more difficult when they have different names.
I also include column names on my PKs and Indexes too where I use _i_ prior to any columns that are in the include clause.
This is a great example of why simple things like naming conventions can save a lot of energy and much more.
Personally, I have found that including column names often makes the constraint or index name too long. However, I tend to use long names to thoroughly describe things. Then I have to resort to some sort of alias for something in order to make the name fit. I have found that a constraint identifying the parent and the child table is sufficient because I can easily identify what the columns are for the primary key or the foreign key columns. Executing the system stored procedure sp_helpconstraint provides all the information handily.
Indexes are another story. I have found that including column names in an index definition are helpful when there are few columns participating in the index. With the advent of included columns in an index I find less need for as many columns in a covering index. This results in better performance of the index due to less sorting requirements, while still providing great benefit found in a covering index. IMHO I think the work Microsoft did with indexes in SQL 2005 and later have been some of the greatest assets to performance.
Thanks for all the great comments.
I guess I’ll have to wait until tomorrow to talk about naming conventions for code. Obviously there are a lot of opinions about what should/shouldn’t be done. I’m going to try and stay away from things such as Camel Case or prefix/suffix considerations. I intend to talk more about naming conventions and how names can help document your code and how it works. If you have some thoughts in that area, feel free to drop me a note at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Office 365 SharePoint Online – Architectural Considerations
Written by AvePoint
The goal of this white paper is to clarify strategies f… (read more)
Featured Script
sp_ShowInformation
Shows information on all database files, locations and sizes…. (read more)