CODEWITHSUNDEEP
databasenaming-conventions
dieter
dieter

Reputation: 2681

Database column naming for foreign key

should I signal the foreign key in a database column name?

FKOrder vs. FK_Order vs. Order

Upvotes: 6

Views: 13105

Answers (5)

Neil Barnwell
Neil Barnwell

Reputation: 42095

The short answer is no - don't put "FK" in column names of foreign key columns. You can still signal the intent of the column though, here's how I do it:

Naming foreign key columns

It depends on your naming convention for the target of the FK. If you have Id, then I'd prepend the table name when creating FK columns.

Example 1:
For table User with PK Id and table Workitem with user ID FK, I'd call the column Workitem.UserId.

If there were more than one FK between the same tables, I'd make this clear in the column name:

Example 2:
For table User with PK Id and table Workitem with "assigned to user ID" and "created by user ID" FKs, I'd call the columns Workitem.CreatedByUserId and Workitem.AssignedToUserId.

If your naming convention for PKs is more like UserId, then you'd factor that into the above examples so as not to end up with UserUserId.

Naming foreign key constraints

This is mine:

FK_childtablename_[differentiator]parenttablename

The differentiator is used when there is more than one FK between the same two tables (e.g. CreatedByUserId and AssignedToUserId). Often I use the child table's column name for this.

Example 1:
Given tables: Workitem and User Where User has CreatedByUserId and AssignedToUserId Foreign key names are FK_Workitem_User_CreatedByUser and FK_Workitem_AssignedToUser

I use double-underscores if tables/columns have underscores in the name:

Example 2:
Given tables: work_item and user Where user has created_by_user_id and assigned_to_user_id Foreign key names are FK_work_item__created_by_user and FK_work_item__assigned_to_user

Upvotes: 8

Harry Joy
Harry Joy

Reputation: 59650

My style is slightly different:

fk_table_column

eg: fk_user_id that is foreign key to User table on id column. I do not use any capital latter.

Upvotes: 0

Albireo
Albireo

Reputation: 11085

You shouldn't.

If a column becomes a foreign key later, you will have to change the column name, breaking all the scripts that are using it.

If there are multiple foreign keys, you don't know which column belongs to which key, so the only information you gain is that the column is a foreign key, but you already know it by looking at the keys.

Usually I name the foreign key column the same as the primary key, so I know immediately where the key maps.

Upvotes: 1

elvenbyte
elvenbyte

Reputation: 776

Is usual to name the foreign key fields with an ID (IDORDER, IDPERSON, ...), if you have a table called PERSONS and another CITIES, if one person is in certain city, CITIES has an IDCITY field (K), PERSONS has a IDPERSON (K), and other field IDCITY (FK).

Hope this answers your question. I mean, a foreign key is only foreign when it's in other table, but not in theirs. But it's a good practice to name always the same to the same fields, even if they are in other tables, as a foreign key.

Upvotes: 1

Oded
Oded

Reputation: 498914

I normally use the same name as the referenced column in the table holding the FK.

Only if this is potentially confusing (or a column with this name already exists, say id), would I be more explicit. In such a case, adding the entity type name before the rest - say ProductId.

Upvotes: 0

Related Questions