Foreign Key naming scheme

Question

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!

Answer 1

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

Answer 2

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.

Answer 3

My usual approach is

FK_ColumnNameOfForeignKey_TableNameOfReference_ColumnNameOfReference

Or in other terms

FK_ChildColumnName_ParentTableName_ParentColumnName

This way I can name two foreign keys that reference the same table like a history_info table with column actionBy and actionTo from users_info table

It will be like

FK_actionBy_usersInfo_name - For actionBy
FK_actionTo_usersInfo_name - For actionTo

Note that:

I didn't include the child table name because it seems common sense to me, I am in the table of the child so I can easily assume the child's table name. The total character of it is 26 and fits well to the 30 character limit of oracle which was stated by Charles Burns on a comment here

Note for readers: Many of the best practices listed below do not work in Oracle because of its 30 character name limit. A table name or column name may already be close to 30 characters, so a convention combining the two into a single name requires a truncation standard or other tricks. – Charles Burns

Answer 4

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

Answer 5

A note from Microsoft concerning SQL Server:

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

so, I'll use terms describing dependency instead of the conventional primary/foreign relationship terms.

When referencing the PRIMARY KEY of the independent (parent) table by the similarly named column(s) in the dependent (child) table, I omit the column name(s):

FK_ChildTable_ParentTable

When referencing other columns, or the column names vary between the two tables, or just to be explicit:

FK_ChildTable_childColumn_ParentTable_parentColumn

Answer 6

If you aren't referencing your FK's that often and using MySQL (and InnoDB) then you can just let MySQL name the FK for you.

At a later time you can find the FK name you need by running a query.