Gorm: ON DELETE for sqlite does not work

[Tarasovych]

What version of Go are you using (go version)?

go1.10.4 linux/amd64

Which database and its version are you using?

SQLite 3.22.0 2018-01-22 18:45:57

Please provide a complete runnable program to reproduce your issue.

package main

import (
    "database/sql"
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)

var (
    err error
    db  *gorm.DB
)

type User struct {
    ID uint `gorm:"primary_key"`

    UserBooks []*UserBook
}

type UserBook struct {
    User   *User
    UserID sql.NullInt64 `sql:"type:integer REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE" gorm:"primary_key"`
    Book   *Book
    BookID sql.NullInt64 `sql:"type:integer REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE" gorm:"primary_key"`
}

func (*UserBook) TableName() string {
    return "user_books"
}

type Book struct {
    ID uint `gorm:"primary_key"`

    UserBooks []*UserBook
}

func init() {
    db, err = gorm.Open("sqlite3", "test.db")
    if err != nil {
        panic(err)
    }
    db.AutoMigrate(&User{}, &Book{}, &UserBook{})
    db.LogMode(true)
}

func main() {
    defer db.Close()

    db.Save(&User{
        UserBooks: []*UserBook{
            {
                Book: &Book{},
            },
        },
    })

    var userBook UserBook
    db.Last(&userBook)
    db.Delete(UserBook{}, "book_id LIKE ?", userBook.BookID.Int64)

    var book Book
    db.Last(&book)
    if book.ID != 0 {
        fmt.Println("ON DELETE does not work")
    }
}

[Adirio]

Try adding db.Exec("PRAGMA foreign_keys = ON") at the end of your init function. SQLite does not have that pragma set by default.

[Tarasovych]

@Adirio sorry, it didn't help. I've tried to add at the end of init(), also before db.AutoMigrate().

[Adirio]

Wait, your example is deleting a UserBook row, why would that delete the Book? Cascade doesn't work like that. Deleting either the Book or the User would cascade and delete the UserBook but not the other way round.

I also noticed you are making the delete quite complex. Try the following.

package main

import (
    "database/sql"
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)

var (
    err error
    db  *gorm.DB
)

type User struct {
    ID uint `gorm:"primary_key"`
    UserBooks []*UserBook
}

type UserBook struct {
    User   *User
    UserID sql.NullInt64 `gorm:"type:integer REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE"`
    Book   *Book
    BookID sql.NullInt64 `gorm:"type:integer REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE"`
}

func (*UserBook) TableName() string {
    return "user_books"
}

type Book struct {
    ID uint `gorm:"primary_key"`
    UserBooks []*UserBook
}

func init() {
    db, err = gorm.Open("sqlite3", "test.db")
    if err != nil {
        panic(err)
    }
    db.AutoMigrate(&User{}, &Book{}, &UserBook{})
        db.Exec("PRAGMA foreign_keys = ON")
    db.LogMode(true)
}

func main() {
    defer db.Close()

    db.Save(&User{
        UserBooks: []*UserBook{
            {
                Book: &Book{},
            },
        },
    })

    var book Book
    db.Last(&book)
        db.Delete(&book)

    var userBook UserBook
    db.Last(&userBook)
    if userBook.ID != 0 {
        fmt.Println("ON DELETE does not work")
    }
}

[Tarasovych]

Cascade doesn't work like that.

@Adirio thanks, I really missed that.

So I changed to:

type User struct {
    ID uint `gorm:"primary_key"`

    UserBooks []*UserBook
}

type UserBook struct {
    User   *User
    UserID uint
    Book   *Book
    BookID uint
}

func (*UserBook) TableName() string {
    return "user_books"
}

type Book struct {
    ID uint `gorm:"primary_key"`

    UserBooks []*UserBook
}

and

func init() {
    db, err = gorm.Open("sqlite3", "test.db")
    if err != nil {
        panic(err)
    }
    db.AutoMigrate(&User{}, &Book{}, &UserBook{})
    db.Model(&UserBook{}).AddForeignKey("user_id", "users(id)", "CASCADE", "NO ACTION")
    db.Model(&UserBook{}).AddForeignKey("book_id", "books(id)", "CASCADE", "NO ACTION")
    db.LogMode(true)
    db.Exec("PRAGMA foreign_keys = ON")
}

As a result, I get

near "CONSTRAINT": syntax error

on rows with AddForeignKey()

[Adirio]

AddForeignKey tries to add the foreign key with a ALTER TABLE ... sql command, which cannot be done in SQLite (source). The error message you are getting has already be mentioned not to be clear enough.

The only way to create foreign keys in SQLite is during table creation, so it must be done with the syntax I told you:

UserID sql.NullInt64 `gorm:"type:integer REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE"`

I edited my previous comment as I did not realize there was a typo, the tag is for gorm not sql, and I'm not sure if multi-columns primary keys are supported, so you may need to introduce a auto-incremented id as primary key in the UserBook type.