Why specify primary / foreign key attributes in column names

Question

A few recent questions discuss strategies for naming columns, and I was rather surprised to discover the concept of embedding the concept of foreign and primary keys in column names. it

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 must admit that I have never worked in any database system that uses such a scheme, and I wonder what are the benefits. As I see it, as soon as you recognize the N main tables of the system, you will write several orders of magnitude more queries with these tables.

To become productive in the development process, you need to know which tables are important tables and which are simple tributaries. You will want to commit a large number of column names in memory. And one of the main tasks is to combine the two tables together. To reduce training efforts, it is easiest 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 believe that as a developer, you do not need to be reminded that there is a lot about which columns are primary keys, which are foreign and which are nothing. Just look at the diagram if you are interested. When you look at random

tx inner join ty on tx.id_bar = ty.id_bar

... is it all important to know which one is the foreign key? Foreign keys are important only for the database engine itself, allowing it to provide referential integrity and do the right thing during updates and deletes.

? ( , , , - ).

Answer 1

, , . , :

SELECT * FROM foo JOIN bar USING (foo_id);

USING , . :

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

, , , , , . , :

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

, . :

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

. "id" .

Answer 2

. Windows.

Answer 3

, 20- , SQL, , . - , .

, , . , .

? -, , . , , , .

, , "id" "_id" .

, customer.id = order.customer_id.

, , , , , "parent_id" "child_id", "parent_person_id" ..

Answer 4

_ , . CustomerId , , CustomerId. , , . Customer.TelephoneNumber, Customer.CustomerId ..

Answer 5

"fk_" , , . , . , , , , .

, , , , , , . , , , , , .

!

Answer 6

- , :

TableNameFieldName, , Customer, UserName - , CustomerUserName. , Invoice, , InvoiceCustomerUserName, , Invoice.CustomerUserName, , .

, , , .

FK_ PK_ ACTUAL .