英文:
GORM and SQL Server: auto-incrementation does not work
问题
我正在尝试使用GORM将新值插入到我的SQL Server表中，但是它一直返回错误。下面是详细的示例：
type MyStructure struct {
ID int32 `gorm:"primaryKey;autoIncrement:true"`
SomeFlag bool `gorm:"not null"`
Name string `gorm:"type:varchar(60)"`
}
执行以下代码（在事务内部使用Create）：
myStruct := MyStructure{SomeFlag: true, Name: "XYZ"}
result := tx.Create(&myStruct)
if result.Error != nil {
return result.Error
}
会导致以下错误：
无法将值NULL插入到列'ID'，表'dbo.MyStructures'；列不允许为空。插入失败
由GORM生成的SQL查询如下所示：
INSERT INTO "MyStructures" ("SomeFlag","Name") OUTPUT INSERTED."ID" VALUES (1, 'XYZ')
另一方面，在DB连接上直接执行Create（不使用事务）会导致以下错误：
表'MyStructures'没有标识属性。无法执行SET操作
由GORM生成的SQL查询如下所示：
SET IDENTITY_INSERT "MyStructures" ON;INSERT INTO "MyStructures" ("SomeFlag", "Name") OUTPUT INSERTED."ID" VALUES (1, 'XYZ');SET IDENTITY_INSERT "MyStructures" OFF;
在这种情况下，我该如何使自动递增功能正常工作？
为什么在事务内外会得到两个不同的错误？
英文:
I am trying to insert new value into my SQL Server table using GORM. However, it keeps returning errors. Below you can find detailed example:
type MyStructure struct {
ID int32 `gorm:"primaryKey;autoIncrement:true"`
SomeFlag bool `gorm:"not null"`
Name string `gorm:"type:varchar(60)"`
}
Executing the code below (with Create inside transaction)
myStruct := MyStructure{SomeFlag: true, Name: "XYZ"}
result = tx.Create(&myStruct)
if result.Error != nil {
return result.Error
}
results in the following error:
> Cannot insert the value NULL into column 'ID', table 'dbo.MyStructures'; column does not allow nulls. INSERT fails
SQL query generated by GORM looks then as follows:
INSERT INTO "MyStructures" ("SomeFlag","Name") OUTPUT INSERTED."ID" VALUES (1, 'XYZ')
On the other hand, executing Create directly on DB connection (without using transaction) results in the following error:
> Table 'MyStructures' does not have the identity property. Cannot perform SET operation
SQL query generated by GORM looks then as follows:
SET IDENTITY_INSERT "MyStructures" ON;INSERT INTO "MyStructures" ("SomeFlag", "Name") OUTPUT INSERTED."ID" VALUES (1, 'XYZ');SET IDENTITY_INSERT "MyStructures" OFF;
How can I make the auto-incrementation work in this case?
Why do I get two different errors depending on whether it is inside or outside transaction?
答案1
得分: 1
我在gorm的问题中找到了这个：
gorm.DefaultCallback.Create().Remove("mssql:set_identity_insert")
https://github.com/go-gorm/gorm/issues/941#issuecomment-250267125
英文:
I find this in gorm's issues:
gorm.DefaultCallback.Create().Remove("mssql:set_identity_insert")
https://github.com/go-gorm/gorm/issues/941#issuecomment-250267125
答案2
得分: 0
只需将您的结构体替换为以下内容：
type MyStructure struct {
ID int32 `gorm:"AUTO_INCREMENT;PRIMARY_KEY;not null"`
SomeFlag bool `gorm:"not null"`
Name string `gorm:"type:varchar(60)"`
}
英文:
Just replace your Struct
type MyStructure struct {
ID int32 `gorm:"primaryKey;autoIncrement:true"`
SomeFlag bool `gorm:"not null"`
Name string `gorm:"type:varchar(60)"`
}
with this
type MyStructure struct {
ID int32 `gorm:"AUTO_INCREMENT;PRIMARY_KEY;not null"`
SomeFlag bool `gorm:"not null"`
Name string `gorm:"type:varchar(60)"`
}
答案3
得分: 0
在结构体中嵌入gorm.Model总是更好的选择，它默认提供了字段：ID, CreatedAt, UpdatedAt, DeletedAt。ID默认为主键，并且是自增的（由GORM管理）。
type MyStructure struct {
gorm.Model
SomeFlag bool `gorm:"not null"`
Name string `gorm:"type:varchar(60)"`
}
删除现有表格：db.Migrator().DropTable(&MyStructure{})，然后重新创建表格：db.AutoMigrate(&MyStructure{})，然后尝试插入记录。
英文:
It's always better to embed the gorm.Model in the struct which gives the fields by default: ID, CreatedAt, UpdatedAt, DeletedAt. ID will be the primary key by default, and it is auto-incremented (managed by GORM)
type MyStructure struct {
gorm.Model
SomeFlag bool `gorm:"not null"`
Name string `gorm:"type:varchar(60)"`
}
Drop the existing table: db.Migrator().DropTable(&MyStructure{}) and create the table again: db.AutoMigrate(&MyStructure{}) and then try to insert the record.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论