Why specify primary/foreign key attributes in column names

foreign-keys, naming-conventions, sql

Solution

I agree with you that the foreign key column in a child table should have the same name as the primary key column in the parent table. Note that this permits syntax like the following:

SELECT * FROM foo JOIN bar USING (foo_id);

The USING keyword assumes that a column exists by the same name in both tables, and that you want an equi-join. It's nice to have this available as shorthand for the more verbose:

SELECT * FROM foo JOIN bar ON (foo.foo_id = bar.foo_id);

Note, however, there are cases when you can't name the foreign key the same as the primary key it references. For example, in a table that has a self-reference:

CREATE TABLE Employees (
  emp_id INT PRIMARY KEY,
  manager_id INT REFERENCES Employees(emp_id)
);

Also a table may have multiple foreign keys to the same parent table. It's useful to use the name of the column to describe the nature of the relationship:

CREATE TABLE Bugs (
  ...
  reported_by INT REFERENCES Accounts(account_id),
  assigned_to INT REFERENCES Accounts(account_id),
  ...
);

I don't like to include the name of the table in the column name. I also eschew the obligatory "id" as the name of the primary key column in every table.

Problem

A couple of recent questions discuss strategies for naming columns, and I was rather surprised to discover the concept of embedding the notion of foreign and primary keys in column names. That is ``` select t1.col_a, t1.col_b, t2.col_z from t1 inner join t2 on t1.id_foo_pk = t2.id_foo_fk ``` I have to confess I have never worked on any database system that uses this sort of scheme, and I'm wondering what the benefits are. The way I see it, once you've learnt the N principal tables of a system, you'll write several orders of magnitude more requests with those tables. To become productive in development, you'll need to learn which tables are the important tables, and which are simple tributaries. You'll want to commit an good number of column names to memory. And one of the basic tasks is to join two tables together. To reduce the learning effort, the easiest thing to do is to ensure that the column name is the same in both tables: ``` select t1.col_a, t1.col_b, t2.col_z from t1 inner join t2 on t1.id_foo = t2.id_foo ``` I posit that, as a developer, you don't need to be reminded that much about which columns are primary keys, which are foreign and which are nothing. It's easy enough to look at the schema if you're curious. When looking at a random ``` tx inner join ty on tx.id_bar = ty.id_bar ``` ... is it all that important to know which one is the foreign key? Foreign keys are important only to the database engine itself, to allow it to ensure referential integrity and do the right thing during updates and deletes. What problem is being solved here? (I know this is an invitation to discuss, and feel free to do so. But at the same time, I am looking for an answer, in that I may be genuinely missing something).

Original source