0

Watch this example for an identifying relationship between project and bug:

Example MySQL Workbench schema

MySQL Workbench creates on identifying relationships not a single primarykey like bug.id for table bug. The primary key in this case consist of bug.id and bug.project_id beacause project is the identifying parent entity for bug.

If I make in this case a n:m table like bug_has_media there are also the primarykey field bug_project_id added with reason project has an 1:n identifying relationship with bug. In this case MySQL Workbench create this bug_project_id field automatically. Is it possible to delete the bug_project_id field in bug_has_media if it's not necessary? Or what is the background and purpose for difference between identifying and non-identifying relationships in MySQL? I know what the meaning of these two relationships is. But not why MySQL Workbench in this situation creates primarykeys based on two fields, and add these two fields in all n:m relations.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Mike
  • 161
  • 1
  • 12
  • `bug.project_id` should not be in the primary key. It should be a non-unique index. – Barmar Feb 03 '18 at 07:36
  • It's normal for a foreign key to refer to the primary key of the referenced table. If you fix the primary key of `bug`, it should do the right thing in `bug_has_media`. – Barmar Feb 03 '18 at 07:39
  • @Barmar But what is the reason on MySQL Workbench then to difference between identifying and non-identifying realtionships? Your suggestion was if I understand it correctly to make only non-identifying realtionships? – Mike Feb 03 '18 at 18:58
  • Sorry, I don't really know anything about MySQL Workbench, and don't really deal with formal models of databases; I had to look up what an identifying relationship is. [This answer](https://stackoverflow.com/a/762994/1491895) points out "it's common practice these days to create a pseudokey for a child table, but not make the foreign key to the parent part of the child's primary key. Formally, the "right" way to do this is to make the foreign key part of the child's primary key." That's what Workbench is doing. – Barmar Feb 04 '18 at 05:53
  • I know this answer and see that before :). But with it it's not finally clear for me what is now the best-practise way to handle these cases with 1:n relationships. I know for n:m tables it is best-practise for handle it often with multiple primary key fields. But in reference to the answer: Is the common practice way the way to go or the formally right way? And you would agree, that it isn't correct to delete some unnecessary fields like bug_project_id if it's part of the multiple fields based primary key? – Mike Feb 05 '18 at 05:38

0 Answers0