Search code examples
databasenaming-conventionsforeign-keys

foreign key constraint naming scheme

What is a standard naming scheme to use for foreign key constraints?

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 key constriants be named?

Does it matter?

Solution

  • 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.