OK, I hope this is an appropriate question for stackoverflow as I'm trying to understand a concept rather than fixing a piece of code that won't work.
I'll take a general example of a form (parent table) and a form field (child table). Logically, this would be an identifying relationship, since a form field cannot exist without a form.
This would make me think that in order to translate the logical relationship into the technical relationship, a simple
NOT NULL for the form_id field in the form_field table would suffice. (see the left part of above screenshot)
However, when I add an identifying relationship using MySQL Workbench, form_id is not only
NOT NULL but also part of the primary key. (see the right part of above screenshot)
And when I add a non-identifying relationship,
NOT NULL is still applied so logically it would actually be an identifying relationship as well.
I guess this confuses me a little, as well as the fact that until now I always simply used the id field as primary key.
So I understand the logical concept of identifying vs. non-identifying relationships, but I don't understand the technical part. Why is it, as this answer states, 'the "right" way to make the foreign key part of the child's primary key'? What is the benefit of these composite primary keys?