仍然对识别与非识别关系感到困惑 [英] Still Confused About Identifying vs. Non-Identifying Relationships

问题描述

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

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:

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

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

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

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

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.

然而，当我读到第二个问题的回答时，我感到困惑，因为它说:如果一个孩子识别了它的父母，那就是一种识别关系."然后，答案继续给出示例，例如 社会安全号码(用于识别个人)，但地址不是(因为很多人可以住在一个地址).对我来说，这听起来更像是主键和非主键之间的决定.

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.

推荐答案

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

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

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

See? book_id is 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.

对 YouTube 视频的评论与相应视频具有识别关系.video_id 应该成为 Comments 表的主键的一部分.

A comment on a YouTube video has an identifying relationship with the respective video. The video_id should be part of the primary key of the Comments table.

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.

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

I would not consider 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.

来自@Niels 的重新评论:

Re comment from @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 ?

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

I suppose so. I hesitate to say yes, because we haven't changed the logical relationship 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.

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

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

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

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

这篇关于仍然对识别与非识别关系感到困惑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！