Foreign Key naming scheme

Question

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.

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

How about FK_TABLENAME_COLUMNNAME?

Keep It Simple Stupid whenever possible.

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