为什么主键的外键部分是标识关系? [英] 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.

为什么，正如 这个答案 所述，正确"的方式是将外键作为孩子的主键'?

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:Y 关系，其中 X >= 1 保证左侧的存在，无论是否识别.在您的情况下，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屋！