SQL Server，不能在主键字段中插入空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3876785/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server, can't insert null into primary key field?
提问by Nick
I'm about ready to rip my hair out on this one. I'm fairly new to MS SQL, and haven't seen a similar post anywhere.
我正准备在这个上扯掉我的头发。我对 MS SQL 相当陌生，还没有在任何地方看到过类似的帖子。
When I try to do a statement like this:
当我尝试做这样的声明时：
INSERT INTO qcRawMatTestCharacteristic
VALUES(NULL, 1,1,1,1,1,1,1,'','','', GETDATE(), 1)
I get the following:
我得到以下信息：
Cannot insert the value NULL into column 'iRawMatTestCharacteristicId', table 'Intranet.dbo.qcRawMatTestCharacteristic'; column does not allow nulls. INSERT fails.
无法将值 NULL 插入列“iRawMatTestCharacteristicId”、表“Intranet.dbo.qcRawMatTestCharacteristic”；列不允许空值。插入失败。
I understand the error, but the null value is for my my primary field with an int data type.
我理解错误，但空值是我的主字段的 int 数据类型。
Any ideas!?
有任何想法吗！？
回答by marc_s
Primary keys in anyrelational database are not allowed to be NULL - it's one of the main, fundamental characteristics of a primary key.
任何关系数据库中的主键都不允许为 NULL - 这是主键的主要基本特征之一。
See: SQL by Design: how to Choose the primary key
Never Null
No primary key value can be null, nor can you do anything to render the primary key null. This is an inviolate rule of the relational model as supported by ANSI, of relational database management system (RDBMS) design, and of SQL Server.
从不为空
主键值不能为空，也不能做任何事情来使主键为空。这是 ANSI 支持的关系模型、关系数据库管理系统 (RDBMS) 设计和 SQL Server 的一项不可侵犯的规则。
UPDATE:ok, so you want an "auto-increment" primary key in SQL Server.
更新：好的，所以您需要 SQL Server 中的“自动增量”主键。
You need to define it as an INT IDENTITY in your CREATE TABLE statement:
您需要在 CREATE TABLE 语句中将其定义为 INT IDENTITY：
CREATE TABLE dbo.YourTable(ID INT IDENTITY, col1 INT, ..., colN INT)
and then when you do an INSERT, you need to explicitly specify the columns to insert, but just don't specify the "ID" column in that list - then SQL Server will handle finding the proper value automagically:
然后当您执行 INSERT 时，您需要明确指定要插入的列，但不要在该列表中指定“ID”列 - 然后 SQL Server 将自动处理查找正确的值：
INSERT INTO dbo.YourTable(col1, col2, ..., colN) -- anything **except** `ID`
VALUES(va1l, val2, ..., valN)
If you want to do this after having created the table already, you can do so in the SQL Server Management Studio's table designer:
如果您想在已经创建表后执行此操作，您可以在 SQL Server Management Studio 的表设计器中执行此操作：
回答by John Hartsock
Primary Key fields cannot contain null values in MS SQL. If you want to populate a SQL table and dont know what to enter for a integer based primary key field then set the pk to an Identity field. Also when specifying Insert statements its wise to use the column mapping portion of the insert statment for example:
MS SQL 中的主键字段不能包含空值。如果您想填充 SQL 表并且不知道为基于整数的主键字段输入什么，则将 pk 设置为标识字段。同样在指定 Insert 语句时，明智的做法是使用 insert 语句的列映射部分，例如：
Insert into (field1, field2, field3)
values
(value1, value2, value3)
The reason for this is it insures that the column order is what you developed for as a SQL administrator can modify column order. It also allows you to insert a row with an identity Primary key with out specifying the value of the Primary Key Example
这样做的原因是它确保列顺序是您开发的，因为 SQL 管理员可以修改列顺序。它还允许您插入具有身份主键的行，而无需指定主键示例的值
CREATE TABLE [dbo].[foo](
[fooid] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
CONSTRAINT [PK_foo] PRIMARY KEY
(
[fooid] ASC
)
now my insert statement is simple
现在我的插入语句很简单
Insert into foo (name)
values
("John")
the result in the table would be
表中的结果将是
1, "John"
回答by Alin Purcaru
You probably don't have (you forgot to add) autoincrement set on your integer primary key.
您可能没有（您忘记添加）在整数主键上设置自动增量。
回答by Shyju
Primary keys shouldnt accept null value.Why you are inserting null values to a primary key field ?Primary key field should have a non-nullable,unique value which will make each of your record in the table unique
主键不应接受空值。为什么要向主键字段插入空值？主键字段应具有不可为空的唯一值，这将使表中的每条记录都是唯一的
回答by Conrad Frix
Assuming you have an autoincrement field for your primary Key you'll need to include the field list on your insert and not put a value for that field e.g.
假设您的主键有一个自动增量字段，您需要在插入中包含字段列表，而不是为该字段输入值，例如
INSERT INTO qcRawMatTestCharacteristic
(Answer1,Answer2,...SomeDateField)
VALUES(1,1,1,1,1,1,1,'','','', GETDATE(), 1)
回答by Spencer Ruport
I'm assuming your real issue is that you're not sure how to write an insert statement so that the PK is auto populated correct? You need to name the fields you're setting values for, it looks like you're trying to set all of them but just exclude the PK field like so:
我假设您的真正问题是您不确定如何编写插入语句以便 PK 自动填充正确？您需要命名要为其设置值的字段，看起来您正在尝试设置所有字段，但只是像这样排除 PK 字段：
INSERT INTO someTable
(fieldName1, fieldName2)
VALUES(1,1)
Where sometable is a table with three fields. PK, fieldName1, and fieldName2. You also need to make sure that the identity property on the PK field is set to true.
sometable 是一个包含三个字段的表。PK、fieldName1 和 fieldName2。您还需要确保 PK 字段上的 identity 属性设置为 true。
回答by sirmak
you can use 0 instead of null for only 1 unique row, null is not possible for PK. Or you can omit PK and use and auto increament PK field
您可以仅对 1 个唯一行使用 0 而不是 null，PK 不可能使用 null。或者您可以省略 PK 并使用和自动增加 PK 字段
回答by DForck42
if you have an identity column, you don't need to specify it in the insert statement.
如果您有标识列，则无需在插入语句中指定它。
INSERT INTO qcRawMatTestCharacteristic
VALUES(1,1,1,1,1,1,1,'','','', GETDATE(), 1)
However, if you have a primary key that isn't an identity column, then you do need to specify it, because otherwise it'll try to insert a null and primary keys by default are non-nullable.
但是，如果您有一个不是标识列的主键，那么您确实需要指定它，否则它会尝试插入一个空值，并且默认情况下主键是不可为空的。
