October 6, 2015 at 12:05 pm
We're working on some Standards, and I was wondering if I could get some advice. Do you typically find any benefit in naming your primary and foreign key constraints? When using Oracle, naming PF and FK constraints was beneficial, as the error message never mentioned the tables themselves. Since those errors did contain the name of the constraint being violated, if you named your primary key constraints something like "<tablename>_pk" and your foreign key constraints "<child_table>_<parent_table>_fk", then you could immediately tell where the problem was.
Since SQL Server gives the actual table names in it's error messages, it's got me thinking that we can just go with the system generated names for these 2 types of constraints. Do you have a contrary suggestion?
Thanks,
--=Chuck
October 6, 2015 at 12:18 pm
Yes, I believe it's a good idea to explicitly name primary and foreign keys. Not only does it provide clarity in error messages, it also makes writing deployment scripts more reliable.
If you allow SQL Server to assign random key names, then the names will be different between environments (ie: Dev vs. QA vs. Production). However, if you name your keys, then when you write DDL scripts for deployment, perhaps something like a script to drop / recreate a key, then the same script will work across environments.
PK_<tablename>
FK_<tablename>_<columnname>
If the foreign key is composite, then I'll use only the leading column. But the exact naming convention you use is not important, so long as it includes the table name.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 6, 2015 at 12:36 pm
I prefer:
tablename__xx_name[&name]|description
where xx is:
PK | CL | IX | CK (check) | TR (trigger) | etc.
Sometimes a column name(s) are not really right and a description is better, such as for triggers and some CHECK conditions.
name is the first key column name, spelled exactly as it in the table (incl. upper/lower case), and optionally other key name(s), separated by &. I admit I copied the & from another DBA, but I've grown to like it, even though it forces delimiters around the name, because it's much easier visually to tell that there are multiple column names. I've just learned to always delimit index/constraint names.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 6, 2015 at 12:52 pm
I like the idea of consistency across environments. Thanks for the help.
--=cf
October 6, 2015 at 12:55 pm
I like the convention child_parent_PK
Where:
child = Name of the table with the foreign key
parent = Name of the table with the primary key being referred to
_PK = constant
For example, if I have an Orders table that has a foreign key to Customers, the foreign key constraint would be named Orders_Customers_PK.
If you have a table with multiple foreign keys to a single table, adjust the name as necessary by adding something else about the foreign key.
I think the important point is that you have some convention, no matter what it is. As long as it makes sense to you and others in your organization, that's what's most important.
October 6, 2015 at 1:04 pm
Ed Wagner (10/6/2015)
I like the convention child_parent_PKWhere:
child = Name of the table with the foreign key
parent = Name of the table with the primary key being referred to
_PK = constant
For example, if I have an Orders table that has a foreign key to Customers, the foreign key constraint would be named Orders_Customers_PK.
If you have a table with multiple foreign keys to a single table, adjust the name as necessary by adding something else about the foreign key.
I think the important point is that you have some convention, no matter what it is. As long as it makes sense to you and others in your organization, that's what's most important.
Hmm. Seems odd to me that a FK would have "_PK" in the name. Esp. as now a fk can reference any unique index, not just specifically a PK.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 6, 2015 at 1:07 pm
ScottPletcher (10/6/2015)
Ed Wagner (10/6/2015)
I like the convention child_parent_PKWhere:
child = Name of the table with the foreign key
parent = Name of the table with the primary key being referred to
_PK = constant
For example, if I have an Orders table that has a foreign key to Customers, the foreign key constraint would be named Orders_Customers_PK.
If you have a table with multiple foreign keys to a single table, adjust the name as necessary by adding something else about the foreign key.
I think the important point is that you have some convention, no matter what it is. As long as it makes sense to you and others in your organization, that's what's most important.
Hmm. Seems odd to me that a FK would have "_PK" in the name. Esp. as now a fk can reference any unique index, not just specifically a PK.
Heh - Good catch, Scott. You're exactly right. I should have typed _FK instead of _PK. Hey, at least I was consistent in my typos. 😛
I use the suffix _PK for primary key constraints, _FK for for foreign key constraints and _UQ for unique constraints.
October 6, 2015 at 4:17 pm
I'd name every object that takes one. Letting the defaults do the naming for you leads to issues down the road, if nothing, in clarity. When you compare your development and production boxes and all the index/Pk/fk/whatever names are different, it makes tracking who changed what when much more difficult.
As far as what naming standard you follow... I really don't care. Go for clarity and consistency and make it easy to use. That's all. After that, I'm easy, child_fk_parent, works, parent_fk_child, works, childparent, works, parentchild, works, parentchild_fk... I'm easy, let's just make it clear and consistent.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply