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?

    Requires Free Membership to View

 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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: