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:
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
Related Q&A from Dan Clamage
A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query. Continue Reading
One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values. Continue Reading
Oracle PL-SQL expert Daniel Clamage answers a question about a problem with doing an “insert” in PL-SQL to create a table dynamically. Continue Reading