Q

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
          ,child_table_name
          ,child_column_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 editor@searchoracle.com

This was first published in March 2010

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close