如果我们使用自动递增的身份列和PK，则违反3NF [英] Violation of 3NF if we use an auto-incremented identity column along with a PK

问题描述

Thomas Connolly和Carolyn Begg在第180页的数据库解决方案第二版中说过：

As it is said in the book of Database Solutions Second Edition written by Thomas Connolly and Carolyn Begg page 180:

第三种形式（3NF）

一个表已经存在于1NF和2NF中，并且在
中，可以从
中计算出所有非主键列中的值，而只有主键列，但没有其他列。

Third normal form (3NF)
A table that is already in 1NF and 2NF, and in which the values in all non-primary-key columns can be worked out from only the primary key column(s) and no other columns.

我已经看到了许多情况，尽管人们已经拥有主键，但他们仍使用身份列表中的列。一条记录也可以从Identity列中得出，因此如果我们在表中使用自动递增的Identity列和主键，是否违反3NF？

I have seen many scenarios where people use an identity column though they already have a primary key column in their table. A record can also be worked out from the identity column, so isn't it a violation of 3NF if we use an auto-incremented identity column along with a primary key in a table?

更新：如果不是，则应将哪个列作为另一个表中的外键引用。主键列还是标识列？

UPDATE: If it is not so which column should be referenced as a foreign key in another table.The primary key column or the Identity column?

推荐答案

那本书《数据库解决方案：2004年第2版的逐步构建数据库指南》一团糟。不幸的是，它说错了话，引起误解，而且他们的许多措辞都非常差劲，例如锻炼，这是非正式的&

That book Database Solutions: A Step by Step Guide to Building Databases 2nd 2004 Edition is a mess. Unfortunately it says wrong things, it misleads, and a lot of their wording is extremely poor--like "work out"--which is informal & never defined.

第三范式（3NF）

已经存在的表在1NF和2NF中，其中所有非主键列中的值都只能从主键列中得出，而不能从其他列中得出。

Third normal form (3NF)
A table that is already in 1NF and 2NF, and in which the values in all non-primary-key columns can be worked out from only the primary key column(s) and no other columns.

该错误定义实际上是非正式的，并且当表只有一个CK（候选键）时。但是该书没有给出间接和以后的说法，而是给出了涉及PK的错误定义（主要是键）：

That wrong definition is actually intended to be informal and for when a table has just one CK (candidate key). But the book doesn't say that except indirectly and later when it gives another wrong definition involving PKs (primary keys):

第三范式（3NF）的正式定义是一个表，该表以前
和第二范式表示并且其中没有非主键列可传递地取决于主键。

The formal definition for third normal form (3NF) is a table that is in first and second normal forms and in which no non-primary-key column is transitively dependent on the primary key.

后来仍然说可以有多个CK，但仍然给出错误的定义：

Later still it says that there can be multiple CKs but it still gives a wrong definition:

因此，对于具有多个候选键的表，您可以使用广义3NF的
定义，该表是1NF和2NF中的表，在
中，可以从
仅候选键列中得出所有非主键列中的值（s），没有其他列。

Therefore, for tables with more than one candidate key you can use the generalized definition for 3NF, which is a table that is in 1NF and 2NF, and in which the values in all the non-primary-key columns can be worked out from only candidate key column(s) and no other columns.

错误地说了主键列，其中主要列即 CK列 wou

It is wrongly saying "primary-key columns" where prime columns ie CK columns would be correct.

他们的另一本书《数据库系统2005年第4版》还介绍了仅存在一个CK时的特殊定义情况，因此，稍后再给出一般定义。至少那些基本属性正确。

Their other book Database Systems 4th Edition 2005 also introduces special cases of definitions for when there is just one CK without saying so then later gives "general" definitions. At least those get "prime attribute" correct.

第三范式（3NF）的一般定义是
中第一和第二范式的关系，没有任何非候选键属性可传递地依赖于任何候选键。

General definition for Third Normal Form (3NF) is a relation that is in First and Second Normal Form in which no non-candidate-key attribute is transitively dependent on any candidate key.

具有任何正常形式的多个CK的表并没有什么异常。

There is nothing unusual about a table having multiple CKs in any normal form.

这篇关于如果我们使用自动递增的身份列和PK，则违反3NF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！