database 识别关系和非识别关系有什么区别？

What's the difference between identifying and non-identifying relationships?

提问by Loc Nguyen

I haven't been able to fully grasp the differences. Can you describe both concepts and use real world examples?

我一直无法完全理解这些差异。你能描述这两个概念并使用现实世界的例子吗？

回答by Bill Karwin

• An identifying relationshipis when the existence of a row in a child table depends on a row in a parent table. This may be confusing because it's common practice these days to create a pseudokey for a child table, but notmake the foreign key to the parent part of the child's primary key. Formally, the "right" way to do this is to make the foreign key part of the child's primary key. But the logical relationship is that the child cannot exist without the parent.

  Example: A Personhas one or more phone numbers. If they had just one phone number, we could simply store it in a column of Person. Since we want to support multiple phone numbers, we make a second table PhoneNumbers, whose primary key includes the person_idreferencing the Persontable.

  We may think of the phone number(s) as belonging to a person, even though they are modeled as attributes of a separate table. This is a strong clue that this is an identifying relationship (even if we don't literally include person_idin the primary key of PhoneNumbers).

• A non-identifying relationshipis when the primary key attributes of the parent must notbecome primary key attributes of the child. A good example of this is a lookup table, such as a foreign key on Person.statereferencing the primary key of States.state. Personis a child table with respect to States. But a row in Personis not identified by its stateattribute. I.e. stateis not part of the primary key of Person.

  A non-identifying relationship can be optionalor mandatory, which means the foreign key column allows NULL or disallows NULL, respectively.

• 一个确定的关系是当一个子表行的存在依赖于父表中的一行。这可能会令人困惑，因为如今为子表创建伪键是常见的做法，但不为子表的主键的父部分创建外键。正式地，执行此操作的“正确”方法是将外键作为子键的主键的一部分。但逻辑关系是，没有父母，孩子就不能存在。

  示例：APerson有一个或多个电话号码。如果他们只有一个电话号码，我们可以简单地将其存储在Person. 由于我们想要支持多个电话号码，我们制作了第二个 table PhoneNumbers，其主键包括person_id引用该Persontable。

  我们可能会认为电话号码属于一个人，即使它们被建模为单独表的属性。这是一个强有力的线索，表明这是一个识别关系（即使我们没有字面上包含person_id在 的主键中PhoneNumbers）。

• 阿非识别关系是当父的主键属性不能成为孩子的主键属性。一个很好的例子是查找表，例如Person.state引用主键的外键States.state。 Person是关于 的子表States。但是一行输入Person不是由其state属性标识的。即state不是 的主键的一部分Person。

  非标识关系可以是可选的或强制的，这意味着外键列分别允许 NULL 或禁止 NULL。

---

---

See also my answer to Still Confused About Identifying vs. Non-Identifying Relationships

另请参阅我对识别与非识别关系仍然感到困惑的回答

回答by Bill Karwin

There is another explanation from the real world:

还有一种来自现实世界的解释：

A book belongs to an owner, and an owner can own multiple books. But, the book can exist also without the owner, and ownership of it can change from one owner to another. The relationship between a book and an owner is a non-identifying relationship.

一本书属于一个所有者，一个所有者可以拥有多本书。但是，这本书也可以在没有所有者的情况下存在，并且它的所有权可以从一个所有者更改为另一个所有者。书和所有者之间的关系是非识别关系。

A book, however, is written by an author, and the author could have written multiple books. But, the book needs to be written by an author - it cannot exist without an author. Therefore, the relationship between the book and the author is an identifying relationship.

然而，一本书是由一个作者写的，作者可以写多本书。但是，这本书需要由作者撰写——没有作者就不可能存在。因此，书与作者之间的关系是一种识别关系。

回答by Daniel Dinnyes

Bill's answeris correct, but it is shocking to seethat among all the other answers no one points out the most significant aspect.

比尔的答案是正确的，但令人震惊的是，在所有其他答案中，没有人指出最重要的方面。

It is said over and over again, that in and identifying relationship the child can not exist without the parent. (e.g. user287724). This is true, but completely misses the point. It would be enough for the foreign key to be non-nullto achieve this. It does not need to be part of the primary key.

人们一遍又一遍地说，在确定关系中，没有父母就不能存在孩子。（例如user287724）。这是事实，但完全没有抓住重点。外键为非空就足以实现这一点。它不需要是主键的一部分。

So here is the real reason:

所以真正的原因是：

The purpose of an identifying relationship is that the foreign key can NEVER CHANGE, because it is part of the primary key... thereforeidentifying!!!

识别关系的目的是外键永远不会改变，因为它是主键的一部分......因此识别！！！

回答by CMS

An Identifying relationship specifies that a child object cannot exist without the parent object

标识关系指定没有父对象就不能存在子对象

Non-identifying relationships specifies a regular association between objects, 1:1 or 1:n cardinality.

非标识关系指定对象之间的常规关联，1:1 或 1:n 基数。

Non-identifying relationships can be specified as optional where a parent is not required or mandatory where a parent is required by setting the parent table cardinality...

通过设置父表基数，可以将非标识关系指定为可选的，其中不需要父项，或者在需要父项的情况下是强制性的...

回答by Andy White

Here's a good description:

这是一个很好的描述：

Relationships between two entities may be classified as being either "identifying" or "non-identifying". Identifying relationships exist when the primary key of the parent entity is included in the primary key of the child entity. On the other hand, a non-identifying relationship exists when the primary key of the parent entity is included in the child entity but not as part of the child entity's primary key. In addition, non-identifying relationships may be further classified as being either "mandatory" or "non-mandatory". A mandatory non-identifying relationship exists when the value in the child table cannot be null. On the other hand, a non-mandatory non-identifying relationship exists when the value in the child table can be null.

两个实体之间的关系可以分类为“识别”或“非识别”。当父实体的主键包含在子实体的主键中时，存在识别关系。另一方面，当父实体的主键包含在子实体中但不作为子实体的主键的一部分时，存在非识别关系。此外，非标识关系可以进一步分类为“强制性”或“非强制性”。当子表中的值不能为空时，存在强制非标识关系。另一方面，当子表中的值可以为空时，存在非强制非标识关系。

http://www.sqlteam.com/article/database-design-and-modeling-fundamentals

http://www.sqlteam.com/article/database-design-and-modeling-fundamentals

Here's a simple example of an identifying relationship:

下面是一个识别关系的简单示例：

Parent
------
ID (PK)
Name

Child
-----
ID (PK)
ParentID (PK, FK to Parent.ID) -- notice PK
Name

Here's a corresponding non-identifying relationship:

这是一个对应的非识别关系：

Parent
------
ID (PK)
Name

Child
-----
ID (PK)
ParentID (FK to Parent.ID) -- notice no PK
Name

回答by Accountant ?

user287724's answergives the following example of the book and author relationship:

user287724 的回答给出了以下书籍和作者关系的示例：

A book however is written by an author, and the author could have written multiple books. But the book needs to be written by an author it cannot exist without an author. Therefore the relationship between the book and the author is an identifying relationship.

然而，一本书是由一个作者写的，作者可以写多本书。但是这本书需要由作者撰写，没有作者就无法存在。因此，书与作者之间的关系是一种识别关系。

This is a very confusing example and is definitely not a valid examplefor an identifying relationship.

这是一个非常混乱的例子，绝对不是一个有效的例子为identifying relationship。

Yes, a bookcan not be written without at least one author, but the author(it's foreign key) of the bookis NOT IDENTIFYINGthe bookin the bookstable!

是的，book不能在没有至少一个写author，但author（它的外键）的book是不标识的book在books表！

You can remove the author(FK) from the bookrow and still can identify the book row by some other field (ISBN, ID, ...etc) , BUT NOT the author of the book!!

您可以author从book行中删除(FK)并且仍然可以通过其他字段（ISBN, ID, ... 等）识别书行，但不是书的作者！

I think a valid example of an identifying relationshipwould be the relationship between (products table) and a (specific product details table) 1:1

我认为一个有效的例子是identifying relationship（产品表）和（特定产品详细信息表）之间的关系1:1

products table
+------+---------------+-------+--------+
|id(PK)|Name           |type   |amount  |
+------+---------------+-------+--------+
|0     |hp-laser-510   |printer|1000    |
+------+---------------+-------+--------+
|1     |viewsonic-10   |screen |900     |
+------+---------------+-------+--------+
|2     |canon-laser-100|printer|200     |
+------+---------------+-------+--------+

printers_details table
+--------------+------------+---------+---------+------+
|Product_ID(FK)|manufacturer|cartridge|color    |papers|
+--------------+------------+---------+---------+------+
|0             |hp          |CE210    |BLACK    |300   |
+--------------+------------+---------+---------+------+
|2             |canon       |MKJ5     |COLOR    |900   |
+--------------+------------+---------+---------+------+
* please note this is not real data

In this example the Product_IDin the printers_detailstable is considered a FK references the products.idtable and ALSO a PKin the printers_detailstable , this is an identifying relationship because the Product_ID(FK) in the printers table IS IDENTIFYINGthe row inside the child table, we can't remove the product_idfrom the child table because we can't identify the row any more because we lost it's primary key

在这个例子中，Product_ID在printers_details台被认为是一个FK引用的products.id表，也是一个PK的printers_details表，这是因为一个确定的关系Product_ID在打印机表（FK）是识别子表中的行，我们不能删除在product_id从子表，因为我们无法确定行了，因为我们失去了它的主键

If you want to put it in 2 lines:

如果你想把它放在两行：

an identifying relationship is the relationship when the FK in the child table is considered a PK(or identifier) in the child table while still references the parent table

识别关系是当子表中的 FK 被认为是子表中的 PK（或标识符）同时仍然引用父表时的关系

Another examplemay be when you have 3 tables (imports - products - countries) in an imports and exports for some country database

另一个例子可能是当您在某个国家/地区数据库的进口和出口中有 3 个表（进口 - 产品 - 国家）时

The importtable is the child that has these fields(the product_id(FK), the country_id(FK) , the amount of the imports , the price , the units imported , the way of transport(air, sea) )we may use the (product_id, thecountry_id`) to identify each row of the imports "if they all in the same year" here the both columns can compose together a primary key in the child table(imports) and also referencing there parent tables.

该import表是具有这些字段（product_id（FK），country_id（FK），进口量，价格，进口单位，运输方式（空运，海运））we may use the (product_id , thecountry_id`）的子项来标识每个导入的行“如果它们都在同一年”，这里的两列都可以将子表（导入）中的主键组合在一起，并引用父表。

Please I'm happy I finally understand the concept of the identifying relationshipand non identifying relationship, so please don't tell me I'm wrong with all of these vote ups for a completely invalid example

请我很高兴我终于理解了identifying relationshipand的概念non identifying relationship，所以请不要告诉我我对所有这些投票都是错误的，因为这是一个完全无效的例子

Yes logically a book can't be written without an author but a book can be identified without the author,In fact it can't be identified with the author!

是的，从逻辑上讲，没有作者就不能写一本书，但没有作者可以识别一本书，实际上无法识别作者！

You can 100% remove the author from the book row and still can identify the book!.

您可以 100% 从书行中删除作者，并且仍然可以识别这本书！.

回答by Skarllot

Non-identifying relationship

非识别关系

A non-identifying relationship means that a child is related to parent but it can be identified by its own.

非识别关系意​​味着孩子与父母有关，但可以通过自己的身份识别。

PERSON    ACCOUNT
======    =======
pk(id)    pk(id)
name      fk(person_id)
          balance

The relationship between ACCOUNT and PERSON is non-identifying.

ACCOUNT 和 PERSON 之间的关系是非识别性的。

Identifying relationship

识别关系

An identifying relationship means that the parent is needed to give identity to child. The child solely exists because of parent.

识别关系意​​味着需要父母为孩子提供身份。孩子完全因为父母而存在。

This means that foreign key is a primary key too.

这意味着外键也是主键。

ITEM      LANGUAGE    ITEM_LANG
====      ========    =========
pk(id)    pk(id)      pk(fk(item_id))
name      name        pk(fk(lang_id))
                      name

The relationship between ITEM_LANG and ITEM is identifying. And between ITEM_LANG and LANGUAGE too.

ITEM_LANG 和 ITEM 之间的关系是识别。在 ITEM_LANG 和 LANGUAGE 之间也是如此。

回答by Daishi

If you consider that the child item should be deleted when the parent is deleted, then it is an identifying relationship.

如果您认为删除父项时应该删除子项，那么它是一种识别关系。

If the child item should be kept even though the parent is deleted, then it is a non-identifying relatio?ship.

如果即使删除了父项也应该保留子项，那么它是一种非识别关系。

As an example, I have a training database with trainees, trainings, diplomas and training sessions :

例如，我有一个包含受训者、培训、文凭和培训课程的培训数据库：

• trainees have an identifying relationship with training sessions
• trainings have an identifying relationship with training sessions
• but trainees have a non-identifying relationship with diplomas

• 受训者与培训课程有识别关系
• 培训与培训课程具有识别关系
• 但受训者与文凭有非身份关系

Only training sessions should be deleted if one of the related trainee, training or diploma is deleted.

如果删除了相关受训者、培训或文凭之一，则仅应删除培训课程。

回答by chanchal dixit

The identifing relaionship means the child entity is totally depend on the existance of the parent entity. Example account table person table and personaccount.The person account table is identified by the existance of account and person table only.

识别关系意​​味着子实体完全依赖于父实体的存在。示例 account 表 person table 和 personaccount。person account 表仅通过 account 和 person 表的存在来标识。

The non identifing relationship means the child table does not identified by the existance of the parent table example there is table as accounttype and account.accounttype table is not identified with the existance of account table.

非识别关系是指子表不通过父表的存在来识别，例如有表为accounttype，account.accounttype 表不通过account表的存在来识别。

回答by Daniel Pinheiro

Like well explained in the link below, an identifying relation is somewhat like a weak entity type relation to its parent in the ER conceptual model. UML style CADs for data modeling do not use ER symbols or concepts, and the kind of relations are: identifying, non-identifying and non-specific.

就像在下面的链接中很好地解释的那样，识别关系有点像 ER 概念模型中与其父级的弱实体类型关系。用于数据建模的 UML 样式 CAD 不使用 ER 符号或概念，其关系类型为：识别、非识别和非特定。

Identifying ones are relations parent/child where the child is kind of a weak entity (even at the traditional ER model its called identifying relationship), which does not have a real primary key by its own attributes and therefore cannot be identified uniquely by its own. Every access to the child table, on the physical model, will be dependent (inclusive semantically) on the parent's primary key, which turns into part or total of the child's primary key (also being a foreign key), generally resulting in a composite key on the child side. The eventual existing keys of the child itself are only pseudo or partial-keys, not sufficient to identify any instance of that type of Entity or Entity Set, without the parent's PK.

识别是父/子关系，其中子是一种弱实体（即使在传统的 ER 模型中也称为识别关系），它自己的属性没有真正的主键，因此不能被它自己唯一地识别. 在物理模型上对子表的每次访问都将依赖于（包含语义上的）父主键，该主键变成子主键的一部分或全部（也是外键），通常导致组合键在儿童方面。子项本身最终存在的密钥只是伪密钥或部分密钥，在没有父项 PK 的情况下，不足以识别该类型实体或实体集的任何实例。

Non-identifying relationship are the ordinary relations (partial or total), of completely independent entity sets, whose instances do not depend on each others' primary keys to be uniquely identified, although they might need foreign keys for partial or total relationships, but not as the primary key of the child. The child has its own primary key. The parent idem. Both independently. Depending on the cardinality of the relationship, the PK of one goes as a FK to the other (N side), and if partial, can be null, if total, must be not null. But, at a relationship like this, the FK will never be also the PK of the child, as when an identifying relationship is the case.

非标识关系是完全独立的实体集的普通关系（部分或全部），其实例不依赖于彼此的主键来唯一标识，尽管它们可能需要外键来实现部分或全部关系，但不需要作为孩子的主键。孩子有自己的主键。父同上。两者独立。根据关系的基数，一个的 PK 作为 FK 到另一个（N 侧），如果部分，可以为空，如果全部，必须不为空。但是，在这样的关系中，FK 永远不会成为孩子的 PK，就像识别关系的情况一样。

http://docwiki.embarcadero.com/ERStudioDA/XE7/en/Creating_and_Editing_Relationships

http://docwiki.embarcadero.com/ERStudioDA/XE7/en/Creating_and_Editing_Relationships