Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks, I always used only one foreign key, but then I saw that a few weeks ago, when I used schemabank, and I was wondering about it. I thought that was some kind of unspoken rule. –  Buzu Jan 27 '12 at 9:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.