GORM and SQL Server: auto-incrementation does not work

user_16630505 0
Viewed 1513

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?

go-gorm go auto-increment insert sql-server
3 Answers
user_161821500

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)"`
}
user_127469580

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.

user_164523530
Related