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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation