Do you need to include all foreing keys of a parent table in the child table?

Question

Suppose you have a table

my_table
-----------
id
name
foreign_id

And you are creating another table that will reference my_table. I think that this would be enough:

other_table
-----------
id
name
my_table_id

However, when creating a diagram on MySQL workbench, if I create a relationship, whether identifying or non-identifying, 1 to 1, or 1 to many, I get this table:

other_table
-----------
id
name
my_table_id
foreign_id

As you can see, both, the id of my_table, and the foreign_key of my_table get added to the new table. Is that really necessary? I would say that other_table.foreign_id is not necessary.

I first noticed this when I was working on schemabank.com (which seems to be gone now, such a shame). And now I noticed it too on MySQL workbench.

Answer 1

I think I follow but here's an example using more meaningful names:

Given these two tables:

CREATE TABLE Persons 
(
 person_ID INTEGER NOT NULL UNIQUE
);

CREATE TABLE Employees 
(
 employee_ID INTEGER NOT NULL UNIQUE, 
 person_ID INTEGER NOT NULL UNIQUE, 
 FOREIGN KEY (person_ID)
    REFERENCES Persons (person_ID), 
);

I think you are saying you would add a referencing table like this:

CREATE TABLE HourlyPaidEmployees
(
 employee_ID INTEGER NOT NULL UNIQUE, 
 FOREIGN KEY (employee_ID)
    REFERENCES Employees (employee_ID) 
);

I would be inclined to agree because I am a human who can understand that given two candidate keys I will choose employee_ID because the table name HourlyPaidEmployees strongly suggests it pertains to employees.

However, it seems your software generates this:

CREATE TABLE HourlyPaidEmployees
(
 employee_ID INTEGER NOT NULL UNIQUE, 
 FOREIGN KEY (employee_ID)
    REFERENCES Employees (employee_ID),
 person_ID INTEGER NOT NULL UNIQUE, 
 FOREIGN KEY (person_ID)
    REFERENCES Employees (person_ID) 
);

I would guess that the software has been designed so that given two candidate keys it should not attempt to 'guess' which one to use and therefore uses both and leaves it to the user to decide which one to remove.