“将IDENTITY_INSERT设置为OFF时，无法为表中的标识列插入显式值".带复合钥匙

问题描述

我们最近在数据库中添加了一个新的级别"-在整个数据库中表中现有ID标识字段的上方/之前添加了一个键"Company_ID".

例如，如果一个表具有ID，则字段，现在它具有Company_ID，然后是ID，然后是字段.这个想法是，这允许为提供给功能的每个不同的Company_ID值自动递增ID(Company_ID 1可以具有ID 1、2、3等; Company_ID 2可以具有ID 1、2、3等)./p>

自动递增"字段保留为ID.表格示例是:

  [dbo].[项目]([Company_ID] [int] NOT NULL，[ID] [int] IDENTITY(1,1)NOT NULL，[DescShort] [varchar](100)NULL，[TypeLookUp_ID] [int] NULL，[StatusLookUp_ID] [int] NULL，[IsActive] [位] NOT NULL，约束[PK_Project]主键已聚集([Company_ID] ASC，[ID] ASC) 

在引入Company_ID之前，要执行CREATE，我们只需填充DescShort，TypeLookUp_ID，StatusLookUp_ID和IsActive字段，并将ID保留为默认值即可，可能为0.

记录已成功保存，并且ID由数据库自动填充，然后用于通过View执行SHOW，依此类推.

但是，现在，我们要将Company_ID设置为指定值，保留ID，然后像以前一样填充字段.

  _db.Project.Add(newProject);_db.SaveChanges(); 

是的，我们要指定Company_ID值.我们希望ID像以前一样自动填充.我们收到错误消息:

这是由指定Company_ID还是由ID字段引起的?您知道我们如何纠正此问题吗?

解决方案

睡觉后，我发现了这个，对于Visual Studio c#代码: [DatabaseGenerated(DatabaseGeneratedOption.Identity)] .为我的每个 ID 字段定义的这个(用我的话来说)告诉Visual Studio该字段是一个Identity，并在将值发送到数据库时将其保留".当 Company_ID 首先出现并具有一个值时，告诉Visual Studio其他地方有一个Identity字段将允许 _db.Project.Add(newProject); 然后是_db.SaveChanges(); 可以按要求运行.答案的这一部分是针对Visual Studio方面的.我了解 IDENTIY_INSERT 的SQL要求，因此感谢@ matt-thrower，@ steve-pettifer和其他贡献者.

We have recently added a new "level" to our database - added a key "Company_ID" to be above/before the existing ID Identity field in the tables throughout the database.

For example, if a Table had ID then fields, it now has Company_ID, then ID, then the fields. The idea is that this allows ID to auto-increment for each different Company_ID value that is provided to the functionality (Company_ID 1 can have ID 1, 2, 3 etc ; Company_ID 2 can have ID 1, 2, 3, etc).

The auto-increment field remains as ID. An example table is :

    [dbo].[Project](
      [Company_ID] [int] NOT NULL,
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [DescShort] [varchar](100) NULL,
      [TypeLookUp_ID] [int] NULL,
      [StatusLookUp_ID] [int] NULL,
      [IsActive] [bit] NOT NULL,
    CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED
    (
      [Company_ID] ASC,
      [ID] ASC
    )

Before the Company_ID was introduced, to perform a CREATE, we simply populated the DescShort, TypeLookUp_ID, StatusLookUp_ID and IsActive fields, and left ID to be whatever it was by default, possibly 0.

The record was saved successfully, and ID was auto-populated by the database, and then used to perform a SHOW via a View, and so on.

Now, however, we want to set Company_ID to a specified value, leave ID, and populate the fields as before.

    _db.Project.Add(newProject);
    _db.SaveChanges();

Yes, we want to specify the Company_ID value. We want the ID to be auto-populated, as per before.We are getting the error message :

Is this caused by specifying the Company_ID, or by the ID field? Do you know how we can rectify this issue?

解决方案

After sleeping, I found this, for Visual Studio c# code : [DatabaseGenerated(DatabaseGeneratedOption.Identity)]. This (in my words), defined for each of my ID fields, tells Visual Studio that the field is an Identity and to "leave it alone" when sending values to the database. When Company_ID occurs first, and has a value, telling Visual Studio that there is an Identity field elsewhere allows the _db.Project.Add(newProject); and then _db.SaveChanges(); to function as required.This part of the answer is for the Visual Studio side of things. I understand the SQL requirements of IDENTIY_INSERT so thanks to @matt-thrower, @steve-pettifer and the others who contributed.

这篇关于“将IDENTITY_INSERT设置为OFF时，无法为表中的标识列插入显式值".带复合钥匙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！