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:
So, for example, the key between notes and tasks would be:
And the key between tasks and users would be:
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:
So you can see that tasks depend on users, and notes depend on both tasks and users.