当我执行下面的脚本时,我有以下错误。错误是关于什么,如何解决?
Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)
错误:
服务器:Msg 544,级别16,状态1,线路1 当IDENTITY_INSERT设置为OFF时,无法为表'table'中的标识列插入显式值。
当我执行下面的脚本时,我有以下错误。错误是关于什么,如何解决?
Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)
错误:
服务器:Msg 544,级别16,状态1,线路1 当IDENTITY_INSERT设置为OFF时,无法为表'table'中的标识列插入显式值。
当前回答
如果您正在使用liquibase更新SQL Server,则可能试图将记录键插入到autoIncrement字段中。通过从插入中删除列,脚本应该可以运行。
<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>
其他回答
另一种情况是检查主键是否与你的类名称相同,唯一的区别是你的主键有一个'ID'附加在它上面,或者在与类如何命名无关的主键上指定[Key]。
我使用asp.net core 5.0,我得到这个错误。我得到这个错误,因为我添加了另一个数据并触发了另一个.SaveChanges()方法,如下所示:
_unitOfWorkVval.RepositoryVariantValue.Create(variantValue);
int request = HttpContext.Response.StatusCode;
if (request == 200)
{
int tryCatch = _unitOfWorkCVar.Complete();
if (tryCatch != 0)
{
productVariant.CategoryVariantID = variantValue.CategoryVariantID;
productVariant.ProductID = variantValue.ProductID;
productVariant.CreatedDate = DateTime.Now;
_unitOfWorkProductVariant.RepositoryProductVariant.Create(productVariant);
_unitOfWorkVval.RepositoryVariantValue.Create(variantValue);
int request2 = HttpContext.Response.StatusCode;
if(request==200)
{
int tryCatch2=_unitOfWorkProductVariant.Complete();//The point where i get that error
}///.......
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)]
你可以简单地使用这句话,例如,如果你的表名是学校。 在插入之前,确保identity_insert设置为ON,在插入查询之后,将identity_insert设置为OFF
SET IDENTITY_INSERT School ON
/*
insert query
enter code here
*/
SET IDENTITY_INSERT School OFF
基本上有两种不同的方法来插入记录而不会出现错误:
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是OFF时,你可以插入新的记录到表中而不会得到一个错误。主键“ID”不能出现在“INSERT INTO”语句中,一个唯一的ID值将被自动添加:。如果在这种情况下,ID从INSERT中出现,您将得到错误“不能为表中标识列插入显式值…”
SET IDENTITY_INSERT [dbo].[Persons] OFF;
INSERT INTO [dbo].[Persons] (FirstName,LastName)
VALUES ('JANE','DOE');
INSERT INTO Persons (FirstName,LastName)
VALUES ('JOE','BROWN');
表[dbo]的输出。[人员]将是:
ID LastName FirstName
1 DOE Jane
2 BROWN JOE
2)在第二个例子中,当IDENTITY_INSERT是ON的时候,你可以插入新的记录到表中而不会得到一个错误。只要ID值不存在,主键“ID”必须从“INSERT INTO”语句中出现:如果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]的输出。[人员]将是:
ID LastName FirstName
1 DOE Jane
2 BROWN JOE
3 BLACK JACK
5 WHITE JOHN