Understanding foreign key column naming conventions

Want to know how to name the foreign key column in your table? This tip from Oracle PL/SQL expert Dan Clamage will help you understand the best way to name a foreign key column.

Do we name the foreign key columns the same as the column they reference in the parent table? How can I find the answer to this question quickly?

 Let’s start with this query:

  SELECT     uc.constraint_name
          ,ucc_child.table_name child_table_name
          ,ucc_child.column_name child_column_name
          ,ucc_parent.table_name parent_table_name
          ,ucc_parent.column_name parent_column_name
FROM       user_constraints uc
           INNER JOIN user_cons_columns ucc_child
             ON (ucc_child.owner = uc.owner
AND              ucc_child.constraint_name = uc.constraint_name
AND              ucc_child.table_name = uc.table_name)
           INNER JOIN user_cons_columns ucc_parent
             ON (uc.r_owner = ucc_parent.owner
AND              uc.r_constraint_name = ucc_parent.constraint_name)
WHERE      uc.constraint_type = 'R'
ORDER BY   parent_table_name


So after I formulated this query, I added this filter to compare the child/parent columns.

 AND ucc_child.column_name != ucc_parent.column_name

Turned out about 64% of our foreign keys did follow this pattern (of making them identical). The rest of the time, the child column was named the same as the parent table, or something else (like a shortened version of the referenced column, or a composite formed by melding the parent and child tables' names). Different developers were inconsistent in formulating and following a naming convention. And having a solid naming convention actually helps you code better and faster (not to mention run faster and jump higher!). For example, when doing a join between the parent and child tables, it's so much easier and sane to write:

 ON (child.key_id = parent.key_id)

Having the foreign key column point to the parent primary or unique key of the same name is intuitive, quicker to code and less error-prone (such as accidentally joining on the wrong columns). At a glance, you can see that the tables are joined correctly.

Have a question for Dan Clamage? Send an e-mail to [email protected]

Dig Deeper on Using Oracle PL-SQL