为什么 SQL 服务器会抛出此错误：无法将值 NULL 插入列“id”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10013313/
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
Why is SQL server throwing this error: Cannot insert the value NULL into column 'id'?
提问by Webnet
I'm using the following query:
我正在使用以下查询：
INSERT INTO role (name, created) VALUES ('Content Coordinator', GETDATE()), ('Content Viewer', GETDATE())
However, I'm not specifying the primary key (which is id). So my questions is, why is sql server coming back with this error:
但是，我没有指定主键（即id）。所以我的问题是，为什么 sql server 会返回此错误：
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'id', table 'CMT_DEV.dbo.role'; column does not allow nulls. INSERT fails.
The statement has been terminated.
回答by Curt
I'm assuming that idis supposed to be an incrementing value.
我假设这id应该是一个递增的值。
You need to set this, or else if you have a non-nullable column, with no default value, if you provide no value it will error.
你需要设置这个，否则如果你有一个不可为空的列，没有默认值，如果你没有提供值，它会出错。
To set up auto-increment in SQL Server Management Studio:
在 SQL Server Management Studio 中设置自动增量：
- Open your table in
Design - Select your column and go to
Column Properties - Under
Indentity Specification, set(Is Identity)=YesandIndentity Increment=1
- 打开你的桌子
Design - 选择您的列并转到
Column Properties - 下
Indentity Specification，设置(Is Identity)=Yes和Indentity Increment=1
回答by Minakshi Korad
use IDENTITY(1,1)while creating the table
eg
IDENTITY(1,1)在创建表时使用，例如
CREATE TABLE SAMPLE(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Status] [smallint] NOT NULL,
CONSTRAINT [PK_SAMPLE] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
回答by Andy Irving
If the idcolumn has no default value, but has NOT NULLconstraint, then you have to provide a value yourself
如果id列没有默认值，但有NOT NULL约束，那么你必须自己提供一个值
INSERT INTO dbo.role (id, name, created) VALUES ('something', 'Content Coordinator', GETDATE()), ('Content Viewer', GETDATE())
回答by robotik
if you can't or don't want to set the autoincrement property of the id, you can set value for the id for each row, like this:
如果您不能或不想设置 id 的自动增量属性，则可以为每一行的 id 设置值，如下所示：
INSERT INTO role (id, name, created)
SELECT
(select max(id) from role) + ROW_NUMBER() OVER (ORDER BY name)
, name
, created
FROM (
VALUES
('Content Coordinator', GETDATE())
, ('Content Viewer', GETDATE())
) AS x(name, created)
回答by natadecoco
you didn't give a value for id. Try this :
你没有给 id 赋值。尝试这个 ：
INSERT INTO role (id, name, created) VALUES ('example1','Content Coordinator', GETDATE()), ('example2', 'Content Viewer', GETDATE())
Or you can set the auto increment on id field, if you need the id value added automatically.
或者您可以在 id 字段上设置自动增量，如果您需要自动添加 id 值。
回答by vid.dev
I had a similar problem and upon looking into it, it was simply a field in the actual table missing id(idwas empty/null) - meaning when you try to make the idfield the primary keyit will result in error because the table contains a row with nullvalue for the primary key.
我有一个类似的问题，在寻找到它，它只是一个字段中的实际表中缺少id（id是empty/null） -这意味着当你试图让id现场的primary key会导致错误，因为表中包含的行null的价值primary key。
This could be the fix if you see a temp table associated with the error. I was using SQL Server Management Studio.
如果您看到与错误相关的临时表，这可能是解决方法。我正在使用 SQL Server Management Studio。
回答by JPTugirimana
In my case,
就我而言，
I was trying to update my model by making a foreign key required, but the database had "null" data in it already in some columns from previously entered data. So every time i run update-database...i got the error.
我试图通过设置一个外键来更新我的模型，但数据库中已经在之前输入的数据的某些列中包含“空”数据。所以每次我运行 update-database 时……我都会收到错误消息。
I SOLVED it by manually deleting from the database all rows that had null in the column i was making required.
我通过从数据库中手动删除所有在我要求的列中为空的行来解决它。
回答by Fandango68
@curt is correct, but I've noticed that sometimes even this fails with NULL disallowed errors, and it seems to be intermittent.
I avoided the error at all times, by also setting the Indenty Seed to 1 and IDENTITY(1, 1) NOT FOR REPLICATION.
@curt 是正确的，但我注意到有时甚至会因 NULL 不允许的错误而失败，而且似乎是间歇性的。我通过将 Indenty Seed 设置为 1 和IDENTITY(1, 1) NOT FOR REPLICATION.
回答by RisingDragon
You need to set autoincrement property of id column to true when you create the table or you can alter your existing table to do this.
创建表时，您需要将 id 列的 autoincrement 属性设置为 true，或者您可以更改现有表来执行此操作。
回答by JupiterP5
You either need to specify an ID in the insert, or you need to configure the id column in the database to have Identity Specification = Yes.
您要么需要在插入中指定 ID，要么需要将数据库中的 id 列配置为 Identity Specification = Yes。
