主键允许NULL - 为什么和在哪个DBMS？ [英] NULL permitted in Primary Key - why and in which DBMS?
问题描述
继续我的问题为什么使用不null主键'在TSQL中？ ...
正如我从其他讨论中理解的，一些RDBMS（例如SQLite， MySQL ）允许主键中的uniqueNULL。
为什么这是允许的，它如何有用？
背景：我相信这有利于沟通
注意
ul>
为了确定主键值的唯一性，
如果INSERT或UPDATE语句试图修改表内容，以使两行或多行具有相同的主要键值，它是一个约束违反。根据SQL标准，PRIMARY KEY应该总是暗示NOT NULL。不幸的是，由于长期的编码监督，SQLite不是这样。
除非该列是INTEGER PRIMARY KEY SQLite允许在PRIMARY KEY列中输入NULL值。我们可以改变SQLite以符合标准（并且我们可能在未来这样做），但是在发现监督的时候，SQLite如此广泛地使用，如果我们修复了这个问题，我们可能会破坏旧代码。
所以现在我们选择继续允许PRIMARY KEY列中的NULL。然而，开发人员应该意识到，我们可能会改变SQLite以符合未来的SQL标准，并应相应地设计新的程序。
- SQL As Understood By SQLite： CREATE TABLE
假设您的主键包含可空列Kn
如果您希望第二行被拒绝，因为在第二行中，Kn为null，表已经包含Kn null的行，那么实际上要求系统将比较row1.Kn = row2.Kn视为给出TRUE（因为您不知何故希望系统检测到这些行中的键值确实相等）。但是，这个比较归结为比较null = null，标准已经明确指定null不等于任何东西，包括自身。
允许你想要的，因此将等于SQL偏离它自己的关于null的处理的原则。在SQL中有无数的不一致，但是这个特定的一个没有通过委员会。
Further to my question "Why to use ´not null primary key´ in TSQL?"...
As I understood from other discussions, some RDBMS (for example SQLite, MySQL) permit "unique" NULL in the primary key.
Why is this allowed and how might it be useful?
Background: I believe it is beneficial for communication with colleagues and database professionals to know the differences in fundamental concepts, approaches and their implementations in different DBMS.
Notes
- MySQL is rehabilitated and returned to the "NOT NULL PK" list.
- SQLite has been added (thanks to Paul Hadfield) to "NULL PK" list:
For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs.
If an INSERT or UPDATE statement attempts to modify the table content so that two or more rows feature identical primary key values, it is a constraint violation. According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a long-standing coding oversight, this is not the case in SQLite.
Unless the column is an INTEGER PRIMARY KEY SQLite allows NULL values in a PRIMARY KEY column. We could change SQLite to conform to the standard (and we might do so in the future), but by the time the oversight was discovered, SQLite was in such wide use that we feared breaking legacy code if we fixed the problem.
So for now we have chosen to continue allowing NULLs in PRIMARY KEY columns. Developers should be aware, however, that we may change SQLite to conform to the SQL standard in future and should design new programs accordingly.
Suppose you have a primary key containing a nullable column Kn.
If you want to have a second row rejected on the ground that in that second row, Kn is null and the table already contains a row with Kn null, then you are actually requiring that the system would treat the comparison "row1.Kn = row2.Kn" as giving TRUE (because you somehow want the system to detect that the key values in those rows are indeed equal). However, this comparison boils down to the comparison "null = null", and the standard already explicitly specifies that null doesn't compare equal to anything, including itself.
To allow for what you want, would thus amount to SQL deviating from its own principles regarding the treatment of null. There are innumerable inconsistencies in SQL, but this particular one never got past the committee.
这篇关于主键允许NULL - 为什么和在哪个DBMS？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
