Naming PK and FK - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Naming PK and FK

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-07-09 : 12:20:36

Hello,

Usually, when I create, let's say DOCS I use ID for the PK and, for example, CategoryID for a FK.

If I have a table where a field is both a PK and a FK what should I call it? ID or CategoryID?

What naming do you use for PK and FK?

Thanks,
Miguel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-09 : 12:31:33

For PK, we use TableNameId (or similar) if it's an identity column. I hate it when ID is used as you'll end up with many ID columns, that all have different meaning. They all mean identity, but they mean identity for different tables.

For FK, we use the same column name as it is in the primary table. So our FK always has the same name as the PK.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-07-09 : 17:30:43

We use two-part Module/Table acronyms.

Order Header Table would be SOP_ORDH_OrderHeader (Part of Sales Order Processing module, table acronym is "ORDH")

Order Items would be SOP_ORDI_OrderItem

All columns in a table start with the module/table acronym (but in lower case)

sop_ordh_ID
sop_ordh_Date
sop_ordh_Status
etc.

Foreign Key, in child record, uses composite module/table acronym of child table plus corresponding parent table's column name:

sop_ordi_sop_ordh_ID - Order item's Order Header ID
sop_ordi_stk_prod_Code = Stock/Product Code
sop_ordi_Qty
...

For PK columns we use:

ID = IDENTITY
Code = Alpha-numeric code; assigned within our system.
Ref = Reference; assigned by a 3rd party (we have no control over it)

Code is forced uppercase, numbers, digits and "_" only

Ref can be anything!

All column names are unique within the whole system. We use the column name for all temporary variables and within the application code - sometimes with prefix or Suffix - e.g. @sop_ordi_ID_MAX might be the MAX Order ID) Thus we can safely do a global find & replace if we change a column name, or do a FIND if we change the datatype/size/attributes of a column to be 99% sure we have found all places where it is used.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-09 : 17:41:07

blindman would say "that's worse than using an alias!".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-12 : 09:13:58

I like short names (because I'm lazy

)

As long as the name is meaningful in context, I don't care what it's called. For identity columns that are PKs I almost always call them ID. If you have 3 fields in your query all named ID they should still be meaningful in context:


SELECT	e.ID employeeId, d.ID departmentID, l.ID locationID
FROM	employee e
JOIN	department d
On	...
JOIN	location l
etc...

it is still clear what each field means.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources