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

User · Answer

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

User · Answer

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

User · Answer

How about FK TABLENAME COLUMNNAME   Keep It Simple Stupid whenever possible

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

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 · Answer

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

User · Answer

How about FK TABLENAME COLUMNNAME   Keep It Simple Stupid whenever possible

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

How about FK TABLENAME COLUMNNAME   Keep It Simple Stupid whenever possible

User · Answer

How about FK TABLENAME COLUMNNAME   Keep It Simple Stupid whenever possible

User · Answer

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

[database] Foreign Key naming scheme

Examples related to database

Examples related to naming-conventions

Examples related to foreign-keys