Search code examples
databasenamingconventionsacid

Renaming DB column in referencing table (violation?)

Say I have this table:

Person table
--------------
PersonId

Address table
------------
AddressId
PersonAddressId

where PersonAddressId is PersonId and the foreign key. Is there any type of database violation in renaming the foreign key? It can become very confusing to work with when they have different names.

Solution

  • It's generally helpful to name the foreign key column the same as the primary key column it references, where possible.

    Of course, sometimes it's not possible:

    • Two columns in Address might both be foreign keys to Person, so obviously you can't name both columns PersonId.
    • Some tables contain a foreign key to itself, e.g. Employee.manager_id could be a reference to Employee.employee_id. Again, you can't name the column the same as the referenced primary key in this scenario.

    There are no strict naming conventions in SQL. One source for suggested metadata naming conventions is ISO 11179.