Foreign Key naming scheme

nickf_9021 0
Viewed 132391

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!

naming-conventions foreign-keys database
10 Answers

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.

Greg_Beech_135520

I use two underscore characters as delimiter i.e. 

fk__ForeignKeyTable__PrimaryKeyTable

This is because table names will occasionally contain underscore characters themselves. This follows the naming convention for constraints generally because data elements' names will frequently contain underscore characters e.g. 

CREATE TABLE NaturalPersons (
   ...
   person_death_date DATETIME, 
   person_death_reason VARCHAR(30) 
      CONSTRAINT person_death_reason__not_zero_length
         CHECK (DATALENGTH(person_death_reason) > 0), 
   CONSTRAINT person_death_date__person_death_reason__interaction
      CHECK ((person_death_date IS NULL AND person_death_reason IS NULL)
              OR (person_death_date IS NOT NULL AND person_death_reason IS NOT NULL))
        ...
onedaywhen_153540

How about FK_TABLENAME_COLUMNNAME?

Keep It Simple Stupid whenever possible.

EvilTeach_77340

I usually just leave my PK named id, and then concatenate my table name and key column name when naming FKs in other tables. I never bother with camel-casing, because some databases discard case-sensitivity and simply return all upper or lower case names anyway. In any case, here's what my version of your tables would look like:

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

Note that I also name my tables in the singular, because a row represents one of the objects I'm persisting. Many of these conventions are personal preference. I'd suggest that it's more important to choose a convention and always use it, than it is to adopt someone else's convention.

Steve_Moyer_170080

Try using upper-cased Version 4 UUID with first octet replaced by FK and '_' (underscore) instead of '-' (dash).

E.g.

  • FK_4VPO_K4S2_A6M1_RQLEYLT1VQYV
  • FK_1786_45A6_A17C_F158C0FB343E
  • FK_45A5_4CFA_84B0_E18906927B53

Rationale is the following

  • Strict generation algorithm => uniform names;
  • Key length is less than 30 characters, which is naming length limitation in Oracle (before 12c);
  • If your entity name changes you don't need to rename your FK like in entity-name based approach (if DB supports table rename operator);
  • One would seldom use foreign key constraint's name. E.g. DB tool usually shows what the constraint applies to. No need to be afraid of cryptic look, because you can avoid using it for "decryption".
user_31121160
Related