为什么外键是识别关系中主键的一部分? [英] Why is the foreign key part of the primary key in an identifying relationship?

问题描述

我试图理解一个概念，而不是修复一段不起作用的代码.

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.

这会让我觉得为了将 逻辑 关系转换为 技术 关系，form_id 字段的简单 NOT NULL在 form_field 表中就足够了.(见上图左侧.)

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.)

但是，当我使用 MySQL Workbench 添加标识关系时，form_id 不仅是 NOT NULL，而且还是主键的一部分.(参见上面屏幕截图的右侧部分.)当我添加一个非标识关系时，NOT NULL 仍然适用，因此在逻辑上它实际上也是一个标识关系.

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.

我想这让我有点困惑，以及直到现在我一直只是简单地使用 id 字段作为主键的事实.

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.

为什么，正如 this answer 所述，使外键成为孩子的主键?

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?

推荐答案

从逻辑上讲，这将是一种识别关系，因为没有表单就不能存在表单域.

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

不，识别关系是关于识别，而不是存在.

No, identifying relationship is about identification, not existence.

任何 X >= 1 的 X:Y 关系都保证左侧存在，无论是否识别.在您的情况下，1:N 关系保证对于任何给定的 form_field 都存在 form.你可以让它识别或不识别，它仍然可以保证相同.

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.

备注:

• 您可以通过使 form_field.form_id 成为键的一部分来模拟识别关系.例如 form_field PK 可能看起来像:{form_id, label}，顺便说一句，这对于正确的 集群 数据(InnoDB 表是 始终聚集).
• 只做一个 PK: {id, form_id} 是不正确的，因为这个超级键不是候选键(即它不是最小的 - 我们可以删除 form_id并仍然保留唯一性).
• 您可以通过将 form_field.form_id 设为 NULL 来模拟 0..1:N 关系(但您将无法使其也能够识别 - 见下文).

• 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).

大多数工具² 似乎都使用严格的定义，所以如果你将关系标记为标识，这将自动使迁移的属性成为子 PK 的一部分，并且没有任何 PK 属性可以为NULL.

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 和 Visio 可以.我还没有使用 MySQL Workbench 进行建模，但您的描述似乎表明它的行为相同.

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

这篇关于为什么外键是识别关系中主键的一部分?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！