问题描述
我在博客、文章、书籍、论坛、SO 等中的 TSQL 中创建表的脚本中经常看到非空主键".
I am seeing too frequently "not null primary key" in scripts creating tables in TSQL in blogs, articles, books, forums, here in SO, etc.
例如，如果搜索not null"NEARprimary key"sql server"，BING 会给出 630,000 个结果((仅英文):
http://www.bing.com/search?q=%22not+null%22+NEAR+%22primary+key%22+%22sql+server%22&go=&form=QBRE&filt=lf
For example, BING gives 630,000 results ((just in English) if to search against "not null" NEAR "primary key" "sql server":
http://www.bing.com/search?q=%22not+null%22+NEAR+%22primary+key%22+%22sql+server%22&go=&form=QBRE&filt=lf
我一直认为NOT NULL"是 PRIMARY KEY 定义不可分割的一部分，至少在 SQL Server 中是这样.
我试图在 SQL Server 中创建一个带有和不带有NOT NULL"的主键的表，但无法掌握可能的区别.
I always perceived "NOT NULL" as inseparable part of definition of PRIMARY KEY, at least in SQL Server.
I tried to create a tables with Primary KEYs with and without "NOT NULL" in SQL Server but could not grasp the possible difference.
为什么即使在快速(简短)插图中也都使用NOT NULL"来创建主键?
Why do all use "NOT NULL" for creating a primary key even in fast (short) illustrations?
更新:
NULL 如何标识任何内容或唯一(以及在允许的情况下防止出现多个 NULL)?它是未指定的、缺失的、不适用的值
Update:
How can NULL identify anything or be unique (as well as preventing multiple NULLs if one is permitted)? It is unspecified, missing, not applicable value
我的问题也暗示了子问题:
如果为 PK 定义了NOT NULL"，为什么不指定 UNIQUE?
PK的定义是什么.是 UNIQUE CONSTRAINT + NOT NULL 还是 UNIQUE INDEX(那么为什么 NOT NULL)?
请给我链接到它的 msdn 文档.
My question also implied subquestions:
if one defines "NOT NULL" for PK, why then UNIQUE is not specified?
And what is the definition of PK. IS it UNIQUE CONSTRAINT + NOT NULL or UNIQUE INDEX (then why NOT NULL)?
Plz give me link to msdn docs on it.
更新 2:@Damien_The_Unbeliever
Update2: @Damien_The_Unbeliever
为什么没有NOT NULL"就不是同义词?
Why is not synonym without "NOT NULL"?
CREATE TABLE T3
(
PK int -- NOT NULL commented out
, nonPK int -- to double-check my sanity
, constraint PK_vgv8 PRIMARY KEY (PK) on [PRIMARY]
)
仍然不允许 NULL 给出:
still does not permit NULL giving:
未更新任何行.
第 2 行中的数据未提交.错误来源:.Net SqlClient 数据提供程序.错误消息:无法将值 NULL 插入到列 'PK'、表 'TestData.dbo.T3' 中；列不允许空值.插入失败.
The data in row 2 was not committed. Error Source: .Net SqlClient Data Provider. Error Message: Cannot insert the value NULL into column 'PK', table 'TestData.dbo.T3'; column does not allow nulls. INSERT fails.
声明已终止.
更正错误并重试或按 ESC 取消更改.
Correct the errors and retry or press ESC to cancel the change(s).
好的帮助
更新 3:
这，概念和术语定义，可能看起来不切实际.
并非如此，对基本概念的一些错误陈述(在沟通/讨论期间其他人的意见)足以被视为白痴，并在专业互动和沟通中造成障碍.
Update3:
This, concepts and terms definition, might appear as impractical nuisance.
It is not, some wrong statement (in the opinion of other(s) during communication/discussion) on a basic notion is enough to be considered a moron and create barriers in professional interaction and communication.
我忘了说，NOT NULL 是由 SSMS 编写的用于 PK 的脚本！
I forgot to tell, NOT NULL is scripted by SSMS for PK!
推荐答案
为清晰起见进行了编辑
根据 SQL 规范，主键不能包含 NULL.这意味着用NOT NULL PRIMARY KEY"或PRIMARY KEY"装饰一列应该做同样的事情.但是您依赖于正确遵循 SQL 标准的 SQL 引擎.例如，由于早期错误，SQL Lite 没有正确实现标准并允许非整数主键中的空值(请参阅 sqlite.org/lang_createtable.html).这意味着(至少)对于 SQLLite 来说，这两个语句会做两件不同的事情.
According to the SQL Specification, a primary key can not contain NULL. This means that decorating a column with either "NOT NULL PRIMARY KEY" or just "PRIMARY KEY" should do the same thing. But you are relying on the SQL engine in question correctly following the SQL standard. As an example due to an early bug, SQL Lite does not correctly implement the standard and allows null values in non-integer primary keys (see sqlite.org/lang_createtable.html). That would mean for (atleast) SQLLite the two statements do two different things.
由于NOT NULL PRIMARY KEY"清楚地表明了意图并且即使主键被删除也继续使用非空值，这应该是首选语法.
As "NOT NULL PRIMARY KEY" clearly indicates intent and continues to enfore non-null values even if the primary key is removed, that should be the prefered syntax.
这篇关于为什么要使用“非空主键"?在 TSQL 中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！