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!
This question is related to
database
naming-conventions
foreign-keys
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
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))
...
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.
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
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.
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.
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
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
How about FK_TABLENAME_COLUMNNAME
?
Keep It Simple Stupid whenever possible.
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
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
How about FK_TABLENAME_COLUMNNAME
?
Keep It Simple Stupid whenever possible.
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))
...
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
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.
How about FK_TABLENAME_COLUMNNAME
?
Keep It Simple Stupid whenever possible.
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.
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.
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
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.
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))
...
Source: Stackoverflow.com