database 仍然对识别与非识别关系感到困惑

Still Confused About Identifying vs. Non-Identifying Relationships

提问by JasCav

So, I've been reading up on identifying vs. non-identifying relationships in my database design, and a number of the answers on SO seem contradicting to me. Here are the two questions I am looking at:

因此，我一直在阅读有关在我的数据库设计中识别与非识别关系的内容，并且关于 SO 的许多答案似乎与我相矛盾。以下是我正在研究的两个问题：

1. What's the Difference Between Identifying and Non-Identifying Relationships
2. Trouble Deciding on Identifying or Non-Identifying Relationship

1. 识别和非识别关系有什么区别
2. 无法确定身份或非身份关系

Looking at the top answers from each question, I appear to get two different ideas of what an identifying relationship is.

查看每个问题的最佳答案，我似乎对什么是识别关系有两种不同的想法。

The first question's response says that an identifying relationship "describes a situation in which the existence of a row in the child table depends on a row in the parent table." An example of this that is given is, "An author can write many books (1-to-n relationship), but a book cannot exist without an author." That makes sense to me.

第一个问题的回答说，识别关系“描述了子表中某行的存在依赖于父表中的行的情况”。给出的一个例子是，“一个作者可以写很多书（1 对 n 的关系），但没有作者就不能存在一本书。” 这对我来说很有意义。

However, when I read the response to question two, I get confused as it says, "if a child identifies its parent, it is an identifying relationship." The answer then goes on to give examples such as Social Security Number(is identifying of a Person), but an address is not (because many people can live at an address). To me, this sounds more like a case of the decision between primary key and non-primary key.

然而，当我读到第二个问题的回答时，我感到困惑，因为它说：“如果一个孩子识别了它的父母，那就是一种识别关系。” 然后答案继续举出诸如社会安全号码（识别一个人）之类的例子，但地址不是（因为许多人可以住在一个地址）。对我来说，这听起来更像是主键和非主键之间的决定。

My own gut feeling (and additional research on other sites) points to the first question and its response being correct. However, I wanted to verify before I continued forward as I don't want to learn something wrong as I am working to understand database design. Thanks in advance.

我自己的直觉（以及对其他网站的额外研究）指出第一个问题及其回答是正确的。但是，我想在继续前进之前进行验证，因为我不想在我正在努力了解数据库设计时学习错误。提前致谢。

回答by Bill Karwin

The technical definition of an identifying relationship is that a child's foreign key is part of its primary key.

识别关系的技术定义是子项的外键是其主键的一部分。

CREATE TABLE AuthoredBook (
  author_id INT NOT NULL,
  book_id INT NOT NULL,
  PRIMARY KEY (author_id, book_id),
  FOREIGN KEY (author_id) REFERENCES Authors(author_id),
  FOREIGN KEY (book_id) REFERENCES Books(book_id)
);

See? book_idis a foreign key, but it's also one of the columns in the primary key. So this table has an identifying relationship with the referenced table Books. Likewise it has an identifying relationship with Authors.

看？ book_id是外键，但它也是主键中的列之一。所以这个表与被引用的表有一个标识关系Books。同样，它与 具有识别关系Authors。

A comment on a YouTube video has an identifying relationship with the respective video. The video_idshouldbe part of the primary key of the Commentstable.

对 YouTube 视频的评论与相应视频具有识别关系。本video_id应是主键的一部分Comments表。

CREATE TABLE Comments (
  video_id INT NOT NULL,
  user_id INT NOT NULL,
  comment_dt DATETIME NOT NULL,
  PRIMARY KEY (video_id, user_id, comment_dt),
  FOREIGN KEY (video_id) REFERENCES Videos(video_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

It may be hard to understand this because it's such common practice these days to use only a serial surrogate key instead of a compound primary key:

可能很难理解这一点，因为如今仅使用串行代理键而不是复合主键是很常见的做法：

CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  video_id INT NOT NULL,
  user_id INT NOT NULL,
  comment_dt DATETIME NOT NULL,
  FOREIGN KEY (video_id) REFERENCES Videos(video_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

This can obscure cases where the tables have an identifying relationship.

这可能会掩盖表具有标识关系的情况。

I would notconsider SSN to represent an identifying relationship. Some people exist but do not have an SSN. Other people may file to get a new SSN. So the SSN is really just an attribute, not part of the person's primary key.

我不会认为 SSN 代表一种识别关系。有些人存在但没有 SSN。其他人可能会申请获得新的 SSN。所以 SSN 实际上只是一个属性，而不是个人主键的一部分。

---

---

Re comment from @Niels:

来自@Niels 的评论：

So if we use a surrogate key instead of a compound primary key, there is no notable difference between use identifying or non-identifying relationship ?

因此，如果我们使用代理键而不是复合主键，那么使用标识关系或非标识关系之间没有显着区别？

I suppose so. I hesitate to say yes, because we haven't changed the logicalrelationship between the tables by using a surrogate key. That is, you still can't make a Comment without referencing an existing Video. But that just means video_id must be NOT NULL. And the logical aspect is, to me, really the point about identifying relationships.

我想是这样。我犹豫着说是，因为我们没有通过使用代理键改变表之间的逻辑关系。也就是说，如果不引用现有视频，您仍然无法发表评论。但这只是意味着 video_id 必须不是 NULL。对我来说，逻辑方面是识别关系的真正重点。

But there's a physical aspect of identifying relationships as well. And that's the fact that the foreign key column is part of the primary key (the primary key is not necessarily a composite key, it could be a single column which is both the primary key of Comments as well as the foreign key to the Videos table, but that would mean you can store only one comment per video).

但也有识别关系的物理方面。这就是外键列是主键的一部分的事实（主键不一定是复合键，它可以是单个列，既是 Comments 的主键又是 Videos 表的外键，但这意味着每个视频只能存储一条评论）。

Identifying relationships seem to be important only for the sake of entity-relationship diagramming, and this comes up in GUI data modeling tools.

识别关系似乎只是为了绘制实体关系图很重要，这在 GUI 数据建模工具中出现。

回答by Erwin Smout

"as I don't want to learn something wrong".

“因为我不想学错”。

Welll, if you really mean that, then you can stop worrying about ER lingo and terminology. It is imprecise, confused, confusing, not at all generally agreed-upon, and for the most part irrelevant.

好吧，如果你真的是这个意思，那么你就可以不用担心 ER 术语和术语了。它是不精确的、混乱的、令人困惑的，根本没有普遍认同的，而且在大多数情况下是无关紧要的。

ER is a bunch of rectangles and straight lines drawn on a piece of paper. ER is deliberately intended to be a means for informalmodeling. As such, it is a valuable first step in database design, but it is also just that : a first step.

ER 是在一张纸上绘制的一堆矩形和直线。ER 有意成为非正式建模的一种手段。因此，这是数据库设计中有价值的第一步，但也仅仅是：第一步。

Never shall an ER diagram get anywhere near the preciseness, accuracy and completeness of a database design formally written out in D.

ER 图永远不会接近用 D 正式写出的数据库设计的精确性、准确性和完整性。

回答by nvogel

Identifying / non-identifying relationships are concepts in ER modelling - a relationship being an identifying one if it is represented by a foreign key that is part of the referencing table's primary key. This is usually of very little importance in relational modelling terms because primary keys in the relational model and in SQL databases do not have any special significance or function as they do in an ER model.

标识/非标识关系是 ER 建模中的概念 - 如果关系由作为引用表主键一部分的外键表示，则该关系是标识关系。这在关系建模术语中通​​常意义不大，因为关系模型和 SQL 数据库中的主键不像它们在 ER 模型中那样具有任何特殊意义或功能。

For example, suppose your table enforces two candidate keys, A and B. Suppose A is also a foreign key in that table. The relationship thus represented is deemed to be "identifying" if A is designated to be the "primary" key, but it is non-identifying if B is the primary key. Yet the form, function and meaning of the table is identical in each case! This is why in my opinion I don't think the identifying / non-identifying concept is really very important.

例如，假设您的表强制执行两个候选键 A 和 B。假设 A 也是该表中的外键。如果 A 被指定为“主”键，则如此表示的关系被认为是“识别”的，但如果 B 是主键，则它是非识别的。然而，表格的形式、功能和含义在每种情况下都是相同的！这就是为什么在我看来，我不认为识别/非识别概念真的很重要。

回答by Pankaj Jha

I believe only difference between an identifying and non identifying relationship is about Nullability of the foreign key. If a FK cannot be NULL, the relationship it represents is identifying (child cannot exist without parent) else it is non identifying.

我相信识别和非识别关系之间的唯一区别在于外键的可空性。如果 FK 不能为 NULL，则它表示的关系是识别性的（没有父级，子代不能存在），否则它是非识别性的。

回答by gnackenson

part of the issue here is the confusion of terminology. identifying relationships are useful for avoiding long join paths.

这里的部分问题是术语的混淆。识别关系对于避免长连接路径很有用。

The best definition i have seen is "an identifying relationship includes the PK as of the parent in the the child PK. In other words the PK of the child includes the FK to the parent as well as the "actual" PK of the child.

我见过的最好的定义是“一个识别关系包括父级在子级 PK 中的 PK。换句话说，子级的 PK 包括对父级的 FK 以及子级的“实际”PK。

回答by Russell Searle

An identifying relationship is indeed an ERD concept as this is the domain of conceptual modelling, modelling our understanding of the 'universe of discourse'. It is a parent-child relationship whereby we model the fact that identity of each child object is (at least in part) established/determined by the identity of the parent object. It is therefore mandatory, and immutable.

识别关系确实是一个 ERD 概念，因为这是概念建模的领域，建模我们对“话语世界”的理解。这是一种父子关系，由此我们对每个子对象的身份（至少部分）由父对象的身份建立/确定的事实进行建模。因此，它是强制性的，并且是不可变的。

A real world example is with the perennial challenge of identifying people. A person's unique identity can be (in part) defined by their relationship with their birth mother and father. When known, these are immutable facts. Therefore the relationship between birth parent and child is an identifying relationship in that it contributes (immutably) to defining the identity of the child.

一个现实世界的例子是识别人的长期挑战。一个人的独特身份可以（部分）由他们与生父的关系来定义。当已知时，这些是不可改变的事实。因此，亲生父母和孩子之间的关系是一种识别关系，因为它有助于（不可改变地）定义孩子的身份。

It is these qualities and the use of relational dbms constructs that result in the PK of the child being a composite key that includes, via FK, the PK of the parent. As a PK, the identity of the child is mandatory and immutable (it can't change) A 'change' in a PK is in fact instantiating a new object. Therefore the PK must not be able to be changed. The immutability of a PK should also be constrained. DB constraints can be used to implement that quality of PKs.

正是这些特性和关系 dbms 结构的使用导致子进程的 PK 成为一个复合键，通过 FK 包括父进程的 PK。作为 PK，孩子的身份是强制性的和不可变的（它不能改变） PK 中的“改变”实际上是实例化一个新对象。因此，PK 必须无法更改。PK 的不变性也应该受到限制。DB 约束可用于实现 PK 的质量。

回答by kumar

identifying relationship gives out one to many optional relationship when we have to define parent to child relationship.in addition it gives one to only one relationship from child to parent flow.since parent entity primary key will be the part of primary key of child entity, child entity instance will identify the parent entity instance.it is represented by solid line in er diagram.

当我们必须定义父子关系时，识别关系给出一对多的可选关系。此外，它给出从子到父流的一对一关系。因为父实体主键将是子实体主键的一部分，子实体实例将标识父实体实例。它在 er 图中用实线表示。

where as non identifying relationship will many to many relationship.For the existence of child entity instance there should have parent entity instance but each entity instance in child entity may be related to many entity instance of parent entity.this is the reason why primary key of parent entity well be the foreign key of child entity, but child entity will not take parent entity primary key as its primary key.it will have its own primary key. many to many relation doesn't exist in real world er diagram. so it need to be resolved

其中非识别关系将是多对多关系。对于子实体实例的存在应该有父实体实例，但是子实体中的每个实体实例可能与父实体的许多实体实例相关。这就是为什么主键的原因父实体可以作为子实体的外键，但是子实体不会以父实体的主键作为主键，它会有自己的主键。现实世界的er图中不存在多对多关系。所以需要解决

回答by marianboda

Yes, go with first one, but i don't think second one contradicts the first one. It's just formulated a little bit confusing..

是的，用第一个，但我不认为第二个与第一个矛盾。它只是制定了一点令人困惑..

UPDATE:

更新：

Just checked - second question's answer is wrong in some assumptions,.. book-author is not necessarily 1:n relation, as it could be m:n. In relational databases that creates intersection table for this m:n relation, and you get identifying relations between intersection table and those other 2 tables..

刚刚检查过 - 第二个问题的答案在某些假设中是错误的，.. book-author 不一定是 1:n 关系，因为它可能是 m:n。在为这个 m:n 关系创建交集表的关系数据库中，你可以识别交集表和其他 2 个表之间的关系。