Foreign Key naming scheme

问题:

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!

回答1:

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.

回答2:

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

回答3:

How about FK_TABLENAME_COLUMNNAME?

Keep It Simple Stupid when ever possible.

回答4:

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.

回答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

回答6:

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

回答7:

This is probably over-kill, but it works for me. It helps me a great deal when I am dealing with VLDBs especially. I use the following:

CONSTRAINT [FK_ChildTableName_ChildColName_ParentTableName_PrimaryKeyColName]

Of course if for some reason you are not referencing a primary key you must be referencing a column contained in a unique constraint, in this case:

CONSTRAINT [FK_ChildTableName_ChildColumnName_ParentTableName_ColumnInUniqueConstaintName]

Can it be long, yes. Has it helped keep info clear for reports, or gotten me a quick jump on that the potential issue is during a prod-alert 100% would love to know peoples thoughts on this naming convention.

回答8:

Based on the answers and comments here, a naming convention which includes the FK table, FK field, and PK table (FK_FKTbl_FKCol_PKTbl) should avoid FK constraint name collisions.

So, for the given tables here:

fk_task_userid_user
fk_note_userid_user

So, if you add a column to track who last modified a task or a note...

fk_task_modifiedby_user
fk_note_modifiedby_user

回答9:

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

回答10:

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.