当IDENTITY_INSERT设置为OFF时，无法为表'table'中的标识列插入显式值。

Question

我执行以下脚本时遇到了下面的错误。这个错误是关于什么的，如何解决它？

Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)

错误:

服务器: Msg 544, 级别 16, 状态 1, 第 1 行

当 IDENTITY_INSERT 设置为 OFF 时，无法向表 'table' 中的标识列插入显式值。

- Jaison

Answer 1

您正在插入一个自增列(identity column)，对于OperationId的值。

您可以通过以下方式在表上启用自增列，以便指定自己的自增值。

SET IDENTITY_INSERT Table1 ON

INSERT INTO Table1
/*Note the column list is REQUIRED here, not optional*/
            (OperationID,
             OpDescription,
             FilterID)
VALUES      (20,
             'Hierachy Update',
             1)

SET IDENTITY_INSERT Table1 OFF 

- pjp

---

将选项打开以允许显式插入，然后进行插入操作，完成后再将该选项关闭。+1代表完全正确，可以翻译成“正好”。 - marc_s

如果您真的希望为标识列添加值，这就是您的答案。但另一方面，有人已将该列设置为在插入时自动增加。在这种情况下，表将跟踪下一个空闲号码，您不需要自己生成OperationID。新的ID可以通过SELECT SCOPE_IDENTITY()获取。 - Hakan Winther

好的回答，但请确保你知道自己在做什么。对我来说，用数据填充数据库以确保外键在不同表之间匹配是很有用的。 - Berty

@UlyssesAlves说，这个选项只能为整个单表数据库打开 - 因此，如果您为一个表打开它，您将无法在其他表中使用它。另外：这不是一个应该保持打开的选项 - 默认情况下，应该让SQL Server处理身份值 - 这仅仅是为了非常特殊的情况而设计的最后手段 - 而不是通用的选项，可以随意开启或关闭... - marc_s

说明：身份标识是一个带有系统生成的唯一升序整数的列，用作记录的唯一标识。通常的原因是创建一个键/编号，如发票号码等，您希望它是唯一的。自己编写身份标识值是很危险的，这会导致创建重复值并破坏表的完整性。您需要清楚自己在做什么，而不仅仅是因为可以将数据插入到表中。这可能用于像替换丢失的数据这样的特殊情况。 - jim birch

Answer 2

如果您在 SQL 服务器上遇到此错误，则运行以下查询：

SET IDENTITY_INSERT tableName ON

这仅适用于数据库中的单个表 例如，如果表名为 student，则查询如下：

SET IDENTITY_INSERT student ON

如果您在您的 Web 应用程序或者您使用了实体框架时遇到此错误，请首先在 SQL 服务器上运行此查询并更新您的实体模型（.edmx 文件），然后构建您的项目，此错误将被解决。

- Umang Patwa

---

在我的MVC Web应用程序中，我遇到了这种情况，使用EF。我只是从.edmx中删除了模型，然后重新添加（右键单击“从数据库更新模型”），并清理和重建项目，这对我有用。谢谢@Umang Patwa。 - Ishwor Khanal

Answer 3

不要给 OperationID 赋值，因为它将自动生成。尝试这样做：

Insert table(OpDescription,FilterID) values ('Hierachy Update',1)

- Wael Dalloul

---

如果您想要自动生成操作ID，这就是正确答案。 - Shaiju T

Answer 4

在将IDENTITY_INSERT设置为ON时要非常谨慎。除非数据库处于维护模式并设置为单用户，否则这是一种不良实践。这不仅会影响您的插入操作，还会影响任何试图访问表格的其他人的插入操作。

为什么要尝试将值放入标识字段中呢？

- HLGEM

---

影响是怎样的？这是一个会话级别的选项，而不是表的属性。 - Martin Smith

在两个需要维护关系的数据库之间进行一次数据同步。例如，我使用它将一个数据库中的产品模式复制到另一个数据库中。 - NSjonas

@NSjonas，这将是使用“set Identity _insert table1 ON”命令的一个很好的例子。我曾经看到有些人想要通过应用程序来完成一些本应该使用简单插入并允许自动生成标识的操作。 - HLGEM

问题是“您能具体说明它是什么以及如何解决吗？”您的答案是一个评论，它提出了一个警告和另一个问题，而不是一个适当的回答来解决问题。 - Quality Catalyst

不要在主键中插入值。 - doflamingo

Answer 5

在该表的实体中，为需要设置标识插入的列添加DatabaseGenerated属性：

示例：

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int TaskId { get; set; }

- user902490

---

嗯，没错，但我真的想要一个关于EF的答案 :-) 不过，对于可能从未使用过甚至从未遇到EF的OP来说，这并不合适。 - Auspex

无论如何，答案都是错误的。我的实体已经使用DatabaseGeneratedOption.Identity指定了，但我仍然收到错误信息。这是我的错，我在新行中放置了伪ID以区分它们在网页上的不同，我希望在insert之前将它们简单地设置为null就足够了。 - Auspex

Answer 6

插入记录有两种基本方法，可以避免出现错误：

1）当IDENTITY_INSERT被设置为OFF时。主键"ID"必须不存在

2）当IDENTITY_INSERT被设置为ON时。主键"ID"必须存在

根据下面的示例，使用具有IDENTITY主键的同一表：

CREATE TABLE [dbo].[Persons] (    
    ID INT IDENTITY(1,1) PRIMARY KEY,
    LastName VARCHAR(40) NOT NULL,
    FirstName VARCHAR(40)
);

1) 在第一个示例中，当IDENTITY_INSERT关闭时，您可以向表中插入新记录而不会出现错误。在"INSERT INTO"语句中不得提供 PRIMARY KEY 的 "ID"，系统将自动添加唯一ID值:如果在此情况下在INSERT中提供了ID，将出现错误"Cannot insert explicit value for identify column in table..."。

SET IDENTITY_INSERT [dbo].[Persons] OFF;
INSERT INTO [dbo].[Persons] (FirstName,LastName)
VALUES ('JANE','DOE'); 
INSERT INTO Persons (FirstName,LastName) 
VALUES ('JOE','BROWN');

表 [dbo].[Persons] 的输出结果如下：

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE

2) 在第二个示例中，当IDENTITY_INSERT为ON时，您可以向表中插入新记录而不会出现错误。 主键“ID”必须存在于“INSERT INTO”语句中，只要ID值不存在：如果在此情况下从INSERT中没有提供ID，则会收到错误消息“必须为标识列指定显式值...”

SET IDENTITY_INSERT [dbo].[Persons] ON;
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (5,'JOHN','WHITE'); 
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (3,'JACK','BLACK'); 

表[dbo].[Persons]的输出结果如下：

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE
3     BLACK      JACK
5     WHITE      JOHN

- Supercoder

---

加一分，因为你真正解释了标志的工作原理。拯救了我的一天，超人。 - John

Answer 7

如果您的表名为School，您可以简单地使用此语句。在插入之前，请确保将identity_insert设置为ON，并在插入查询后将identity_insert关闭OFF

SET IDENTITY_INSERT School ON
/*
  insert query
  enter code here
*/
SET IDENTITY_INSERT School OFF

- user5948411

---

您能否稍微详细解释一下您的答案，包括该命令的解释、原理和作用？ - gareththegeek

@gareththegeek 是的，这是用于标识列的，你必须确保在插入数据时将其添加进去。 - user5948411

Answer 8

请注意，如果您在每行末尾使用 ;，则 SET IDENTITY_INSERT mytable ON 命令不会对后面的行起作用。

即。

例如，查询如下：

SET IDENTITY_INSERT mytable ON;
INSERT INTO mytable (VoucherID, name) VALUES (1, 'Cole');

出现错误：
当IDENTITY_INSERT设置为OFF时，在表'mytable'中无法插入显式值。

但是像这样的查询将会成功：

SET IDENTITY_INSERT mytable ON
INSERT INTO mytable (VoucherID, name) VALUES (1, 'Cole')
SET IDENTITY_INSERT mytable OFF;

似乎 SET IDENTITY_INSERT 命令只在事务中生效，而 ; 则表示事务的结束。

- cryanbhu

Answer 9

大家都谈论SQL，但EntityFramework怎么了？我读完整篇文章后发现没有人解决EF的问题。所以几天后，我终于找到了解决方案：

在创建模型时，EF Core上下文中有一条指令：modelBuilder.Entity<Cliente>(entity => { entity.Property(e => e.Id).ValueGeneratedNever();

这也会产生错误，解决方法：将其更改为ValueGeneratedOnAdd()，然后它就可以运行了！

- Alfonso Castañeda

Answer 10

如果您正在使用liquibase更新SQL Server，您可能正在尝试将记录键插入到自动递增字段中。通过从插入中删除该列，您的脚本应该可以运行。

<changeSet id="CREATE_GROUP_TABLE" >
    <createTable tableName="GROUP_D">
        <column name="GROUP_ID" type="INTEGER" autoIncrement="true">
            <constraints primaryKey="true"/>
        </column>
    </createTable>
</changeSet>

<changeSet id="INSERT_UNKNOWN_GROUP" >
    <insert tableName="GROUP_D">    

        <column name="GROUP_ID" valueNumeric="-1"/>
 ...
    </insert>
</changeSet>

- iowatiger08