Foreign Key naming scheme

databaseforeign-keysnaming-conventions

I'm just getting started working with foreign keys for the first time and I'm wondering if there's a standard naming scheme to use for them?

Given these tables:

task (id, userid, title)
note (id, taskid, userid, note);
user (id, name)

Where Tasks have Notes, Tasks are owned by Users, and Users author Notes.

How would the three foreign keys be named in this situation? Or alternatively, does it even matter at all?

Update: This question is about foreign key names, not field names!

Best Answer

The standard convention in SQL Server is:

FK_ForeignKeyTable_PrimaryKeyTable

So, for example, the key between notes and tasks would be:

FK_note_task

And the key between tasks and users would be:

FK_task_user

This gives you an 'at a glance' view of which tables are involved in the key, so it makes it easy to see which tables a particular one (the first one named) depends on (the second one named). In this scenario the complete set of keys would be:

FK_task_user
FK_note_task
FK_note_user

So you can see that tasks depend on users, and notes depend on both tasks and users.

Related Topic