sql - Why is the foreign key part of the primary key in an identifying relationship?

Question

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?

Answer 1

Logically, this would be an identifying relationship, since a form field cannot exist without a form.

No, identifying relationship is about identification, not existence.

Any X:Y relationship where X >= 1 guarantees existence of the left side, whether identifying or not. In your case, a 1:N relationship guarantees existence of form for any given form_field. You could make it identifying or non-identifying and it would still guarantee the same.

Remarks:

• You would model an identifying relationship by making form_field.form_id part of a key. For example form_field PK could look like: {form_id, label}, which BTW would be quite beneficial for proper clustering of your data (InnoDB tables are always clustered).
• Just making a PK: {id, form_id} would be incorrect, since this superkey is not a candidate key (i.e. it is not minimal - we could remove form_id from it and still retain the uniqueness).
• You would model a 0..1:N relationship by making the form_field.form_id NULL-able (but then you wouldn't be able to make it identifying as well - see below).

There are two definitions of the "identifying relationship":

• Strict definition: A relationship that migrates parent key into child primary key¹.
• Loose definition: A relationship that migrates parent key into child key.

In other words, the loose definition allows migration into alternate key as well (and not just primary).

Most tools² seem to use the strict definition though, so if you mark the relationship as identifying, that will automatically make the migrated attributes part of the child PK, and none of the PK attributes can be NULL.

---

¹ Which is then either completely comprised from migrated attributes, or is a combination of migrated attributes and some additional attributes.

² ERwin and Visio do. I haven't used MySQL Workbench for modeling yet, but your description seems to suggest it behaves the same.