当我执行下面的脚本时,我有以下错误。错误是关于什么,如何解决?

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

错误:

服务器:Msg 544,级别16,状态1,线路1 当IDENTITY_INSERT设置为OFF时,无法为表'table'中的标识列插入显式值。


当前回答

即使一切正确,如果Identity列的数据类型不是int或long,也会出现此错误。我有单位列为十进制,虽然我只保存int值(我的坏)。更改数据库和底层模型中的数据类型为我解决了这个问题。

其他回答

如果您使用Oracle SQL Developer进行连接,请记住添加/sqldev:stmt/

/sqldev:stmt/ set identity_insert TABLE on

EF Core 3.x

引用Leniel Maccaferri,我有一个带有自动递增属性的表,称为ID(原始主键)和另一个属性称为Other_ID(新的主键)。最初ID是主键,但后来Other_ID需要成为新的主键。由于ID正在应用程序的其他部分使用,我不能只是从表中删除它。Leniel Maccaferri解决方案只适用于我之后,我添加了以下片段:

        entity.HasKey(x => x.Other_ID);
        entity.Property(x => x.ID).ValueGeneratedOnAdd();

完整代码片段如下(ApplicationDbContext.cs):

  protected override void OnModelCreating(ModelBuilder builder)
  {
        builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
        base.OnModelCreating(builder);
        builder.Entity<tablename>(entity =>
        {
            entity.HasKey(x => x.Other_ID);
            entity.Property(x => x.ID).ValueGeneratedOnAdd();
            entity.HasAlternateKey(x => new { x.Other_ID, x.ID });                
        });
        
    }

This occurs when you have a (Primary key) column that is not set to Is Identity to true in SQL and you don't pass explicit value thereof during insert. It will take the first row, then you wont be able to insert the second row, the error will pop up. This can be corrected by adding this line of code [DatabaseGenerated(DatabaseGeneratedOption.Identity)] in your PrimaryKey column and make sure its set to a data type int. If the column is the primary key and is set to IsIDentity to true in SQL there is no need for this line of code [DatabaseGenerated(DatabaseGeneratedOption.Identity)] this also occurs when u have a column that is not the primary key, in SQL that is set to Is Identity to true, and in your EF you did not add this line of code [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

首先转到所需的表名

步骤2 ->右击表名

选择设计

单击列名

步骤5)进入列属性,然后将No设置为Identity Specification

[注意:插入到显式值后,如果你想,你可以恢复到标识规范为true,然后它会再次生成值]

如果你使用SQL server管理studio你可以使用下面的方法

步骤1) 步骤2)

把断点放在你的[HttpPost]方法上,检查什么值被传递给Id属性。如果它不为零,则会发生此错误。