MySQL 无法确定身份关系还是非身份关系

Trouble deciding on identifying or non-identifying relationship

提问by KdgDev

I've read this question: What's the difference between identifying and non-identifying relationships?

我读过这个问题：识别和非识别关系有什么区别？

But I'm still not too sure... What I have is three tables.

但我还是不太确定……我有三张桌子。

1. Users
2. Objects
3. Pictures

1. 用户
2. 对象
3. 图片

A user can own many objects and can also post many pictures per individual object. My gut feeling tells me this is an identifying relationship, because I'll need the userID in the objects table and I'll need the objectID in the pictures tables...

用户可以拥有许多对象，也可以为每个对象发布许多图片。我的直觉告诉我这是一个识别关系，因为我需要对象表中的用户 ID，我需要图片表中的对象 ID...

Or am I wrong? The explanations in the other topic limit themselves to the theoretical explanation of the way the database interprets it after it's already been coded, not how the objects are connected in real life. I'm kinda confused as to how to make the decision of identifying versus non-identifying when thinking about how I'm going to build the database.

还是我错了？另一个主题中的解释仅限于对数据库在编码后对其进行解释的方式的理论解释，而不是对象在现实生活中的连接方式。在考虑如何构建数据库时，我对如何做出识别与非识别的决定感到有些困惑。

回答by Nicole

Both sound like identifying relationships to me. If you have heard the terms one-to-one or one-to-many, and many-to-many, one-to- relationshipsare identifying relationships, and many-to-many relationshipsare non-identifying relationships.

两者听起来都像是在确定与我的关系。如果您听说过的条款一到一对一或一对多，和许多一对多，一对一关系的确定关系，并多到许多关系都是非识别关系。

• If the child identifies its parent, it is an identifying relationship. In the link you have given, if you have a phone number, you know who it belongs to (it only belongs to one).

• If the child does not identify its parent, it is a non-identifying relationship. In the link, it mentions states. Think of a state as a row in a table representing mood. "Happy" doesn't identify a particular person, but many people.

• 如果孩子识别其父母，则这是一种识别关系。在您提供的链接中，如果您有电话号码，您就知道它属于谁（它只属于一个人）。

• 如果孩子不识别其父母，则这是一种非识别关系。在链接中，它提到了状态。将状态视为表格中代表心情的一行。“快乐”不是指特定的人，而是指很多人。

Edit: Other real life examples:

编辑：其他现实生活中的例子：

• A physical address is a non-identifying relationship, because many people may reside at one address. On the other hand, an email address is (usually considered) an identifying relationship.
• A Social Security Number is an identifying relationship, because it only belongs to one person
• Comments on Youtube videos are identifying relationships, because they only belong to one video.
• An original of a painting only has one owner (identifying), while many people may own reprints of the painting (non-identifying).

• 实际地址是一种非识别关系，因为许多人可能居住在一个地址。另一方面，电子邮件地址（通常被认为）是一种识别关系。
• 社会安全号码是一种识别关系，因为它只属于一个人
• 对 Youtube 视频的评论是识别关系，因为它们只属于一个视频。
• 一幅画的原作只有一个所有者（身份），而许多人可能拥有这幅画的重印本（非身份）。

回答by Rick Sumner

I think that an easier way to visualize it is to ask yourself if the child record can exist without the parent. For example, an order line item requires an order header to exist. Thus, an order line item must have the order header identifier as part of its key and hence, this is an example of an identifying relationship.
On the other hand, telephone numbers can exist without ownership of a person, although a person may have several phone numbers. In this case, the person who owns the phone number is a non-key or non-identifying relationship since the phone numbers can exist irrespective of the owner person (hence, the phone number owner person can be null whereas in the order line item example, the order header identifier cannot be null.

我认为一种更简单的可视化方法是问问自己子记录是否可以在没有父记录的情况下存在。例如，订单行项目需要存在订单标题。因此，订单行项目必须将订单标题标识符作为其键的一部分，因此，这是识别关系的一个示例。
另一方面，虽然一个人可能有多个电话号码，但电话号码可以在没有个人所有权的情况下存在。在这种情况下，拥有电话号码的人是非关键或非识别关系，因为电话号码可以存在而与所有者个人无关（因此，电话号码所有者个人可以为空，而在订单行项目示例中，订单头标识符不能为空。

回答by Mahdi

NickC Said: one-to- relationships are identifying relationships, and many-to-many relationships are non-identifying relationships

NickC Said：一对关系是识别关系，多对多关系是非识别关系

The explanation seems totally wrong to me. You can have:

这个解释对我来说似乎完全错误。你可以有：

• Ono-to-One Non-identifying Relationships
• One-to-Many Non-identifying Relationships
• One-to-One Identifying Relationships
• One-to-Many Identifying Relationships
• Many-to-Many Identifying Relationships

• 一对一的非识别关系
• 一对多非标识关系
• 一对一识别关系
• 一对多识别关系
• 多对多识别关系

Imagine you have the following tables: customer, productsand feedback. All of them are based on the customer_idwhich exists on the cutomertable. So, by NickCdefinition there shouldn't be exists any kind of Many-to-Many Identifying Relationships, however in my example, you can clearly see that: A Feedback can exists only if the relevant Product exists and has been bought by the Customer, so Customer, Products and Feedback should be Identifying.

假设您有以下表：customer、products和feedback。所有这些都是基于表上customer_id存在的cutomer。因此，根据NickC 的定义，不应该存在任何类型的多对多识别关系，但是在我的示例中，您可以清楚地看到：只有当相关产品存在且已被客户购买时，反馈才能存在，所以客户、产品和反馈应该是识别。

You can take a look at MySQL Manual, explaining how to add Foreign Keys on MySQL Workbench as well.

您可以查看MySQL 手册，解释如何在 MySQL Workbench 上添加外键。

回答by rkedge

Mahdi, your instincts are correct. This is a duplicate question and this up-voted answer is not correct or complete. Look at the top two answers here: difference between identifying non-identifying

马赫迪，你的直觉是正确的。这是一个重复的问题，这个高票答案不正确或不完整。看看这里的前两个答案： 识别非识别之间的区别

Identifying vs non-identifying has nothing to do with identity. Simply ask yourself can the child record exist without the parent? If the answer is yes, the it is non-identifying.

识别与非识别与身份无关。简单地问自己是否可以在没有父记录的情况下存在子记录？如果答案是肯定的，则它是非识别性的。

The core issue whether the primary key of the child includes the foreign key of the parent. In the non-identifying relationship the child's primary key (PK) cannot include the foreign key (FK).

核心问题是子键的主键是否包含父键的外键。在非标识关系中，孩子的主键 (PK) 不能包含外键 (FK)。

Ask yourself this question

问自己这个问题

• Can the child record exist without the parent record?

• 子记录可以在没有父记录的情况下存在吗？

If the child can exist without the parent, then the relationship is non-identifying. (Thank you MontrealDevOnefor stating it more clearly)

如果孩子可以在没有父母的情况下存在，那么这种关系是非识别性的。（感谢MontrealDevOne更清楚地说明了这一点）

One-to-one identifying relationship

一对一识别关系

Social security numbers fit nicely in to this category.Let's imagine for example that social security numbers cannot exist with out a person (perhaps they can in reality, but not in our database) The person_idwould be the PK for the persontable, including columns such as a nameand address. (let's keep it simple). The social_security_numbertable would include the ssncolumn and the person_idcolumn as a foreign key. Since this FK can be used as the PK for the social_security_numbertable it is an identifying relationship.

社会安全号码非常适合这一类别。例如，让我们想象一下，如果没有一个人，社会安全号码就不能存在（也许在现实中可以，但在我们的数据库中不能）person_id将是person表的 PK ，包括诸如name和address 之类的列。（让我们保持简单）。该social_security_number表将包括SSN列和为person_id列作为外键。由于此 FK 可用作social_security_number表的 PK，因此它是一种识别关系。

One-to-one non-identifying relationship

一对一的非识别关系

At a large office complex you might have an officetable that includes the room numbers by floor and building number with a PK, and a separate employeetable. The employee table (child) has a FK which is the office_idcolumn from the officetable PK. While each employee has only one office and (for this example) every office only has one employee this is a non-identifying relationship since offices can exist without employees, and employees can change offices or work in the field.

在大型办公大楼中，您可能有一张办公桌，其中包含按楼层和建筑物编号的房间号和 PK，以及一个单独的员工表。员工表（子表）有一个 FK，它是office表 PK 中的office_id列。虽然每个员工只有一个办公室，并且（在本例中）每个办公室只有一个员工，但这是一种非识别关系，因为办公室可以没有员工而存在，而员工可以更换办公室或在现场工作。

One-to-many relationships

一对多关系

One-to-many relationships can be categorized easily by asking the same question.

通过提出相同的问题，可以轻松地对一对多关系进行分类。

Many-to-many relationships

多对多关系

Many-to-many relationships are always identifying relationships. This may seem counter intuitive, but bear with me. Take two tables libaryand books, each library has many books, and a copy of each book exists in many libraries.

多对多关系总是标识关系。这可能看起来违反直觉，但请耐心等待。拿两张表libary和books，每个图书馆有很多书，每本书的副本存在于许多图书馆。

Here's what makes it and identifying relationship:In order to implement this you need a linking table with two columns which are the primary keys of each table. Call them the library_idcolumn and the ISBNcolumn. This new linking table has no separate primary key, but wait! The foreign keys become a multi-column primary key for the linking table since duplicate records in the linking table would be meaningless. The linkscannot exist with out the parents; therefore, this is an identifying relationship. I know, yuck right?

这是它的构成和识别关系：为了实现这一点，您需要一个包含两列的链接表，这些列是每个表的主键。称它们为library_id列和ISBN列。这个新的链接表没有单独的主键，但是等等！外键成为链接表的多列主键，因为链接表中的重复记录将毫无意义。该链接不能让那些家长存在; 因此，这是一种识别关系。我知道，是吗？

Most of the time the type of relationship does not matter.

大多数时候，关系的类型并不重要。

All that said, usually you don't have to worry about which you have. Just assign the proper primary and foreign keys to each table and the relationship will discover itself.

综上所述，通常您不必担心您拥有哪些。只需为每个表分配适当的主键和外键，关系就会自行发现。

EDIT: NicoleC, I read the answeryou linked and it does agree with mine. I take his point about SSN, and agree that is a bad example. I'll try to think up another clearer example there. However if we start to use real-world analogies in defining a database relationship the analogies always break down. It matters not, whether an SSN identifies a person, it matters whether you used it as a foreign key.

编辑：NicoleC，我阅读了您链接的答案，它确实与我的一致。我接受他关于 SSN 的观点，并同意这是一个不好的例子。我会尝试在那里想出另一个更清晰的例子。然而，如果我们开始使用现实世界的类比来定义数据库关系，类比总是会失效。SSN 是否识别一个人并不重要，重要的是您是否将其用作外键。