Renaming DB column in referencing table (violation?)

Question

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.

Answer 1

Agreed and that's why the convention is to name PersonAddressId as PersonId.

Answer 2

Thanks. Where can I find documentation that on the rule? I'd like something to support it other than tribal knowledge (or passed down best practice).

Answer 3

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.