Gorm: SQLite AddForeignKey - syntax error

[jkblume]

Hello,

I want to declare a foreign key in my data model. But after calling the AddForeignKey function i get an syntax error. After a little research i found that ALTER TABLE ... ADD CONSTRAINT is not supported by sqlite3 [https://www.sqlite.org/omitted.html].

Is there a workaround or is it possible to declare the foreignkey per tag, so that it is generated with the CREATE TABLE call?

package main

import (
    "github.com/jinzhu/gorm"
    _ "github.com/lib/pq"
    _ "github.com/mattn/go-sqlite3"
)

var db gorm.DB

func Init() {
    var err error
    db, err = gorm.Open("sqlite3", "test.db")
    if err != nil {
        panic(err)
    }
    db.LogMode(true)
}

type User struct {
    ID   uint
    Role Role
}

type Role struct {
    ID     uint
    UserID uint
}

func main() {
    Init()
    db.CreateTable(&User{})
    db.CreateTable(&Role{})
    db.Model(&Role{}).AddForeignKey("UserID", "User(ID)", "RESTRICTED", "RESTRICTED")
}

[jinzhu]

Hi @jakblu

You could create foreign key for sqlite with tag like this:

package main

import (
    "fmt"

    "github.com/jinzhu/gorm"
    _ "github.com/lib/pq"
    _ "github.com/mattn/go-sqlite3"
)

var db gorm.DB

func init() {
    var err error
    db, err = gorm.Open("sqlite3", "test.db")
    if err != nil {
        panic(err)
    }
    db.LogMode(true)
}

type User struct {
    ID   uint
    Name string
    Role Role
}

type Role struct {
    ID     uint
    Name   string
    UserID uint `sql:"type:integer REFERENCES users(id)"`
}

func main() {
    db.DropTable(&User{})
    db.DropTable(&Role{})
    db.CreateTable(&User{})
    db.CreateTable(&Role{})
    db.Exec("PRAGMA foreign_keys = ON;")

    if err := db.Create(&User{
        Name: "user",
        Role: Role{
            Name: "admin",
        },
    }).Error; err != nil {
        fmt.Print(err)
    }

    if err := db.Create(&Role{
        Name: "invalid",
    }).Error; err != nil {
        fmt.Print(err)
    }
}

[deepnirmal]

@jinzhu this works but what if I have MySQL for production and SQLite for running the test cases.
Any thoughts?

[jakubknejzlik]

IMHO this issue deserves to be open as the usecase from @deepnirmal is not just about test cases, but also one of the powers of ORM (to be database-independent).

As for the possible solution and also suggested way from SQLite itself theres possibility to add foreign keys (basically by creating new table with foreign key and transfer data from previous table):

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO _employees_old;

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  department_id INTEGER,
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

INSERT INTO employees SELECT * FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

Source: https://www.techonthenet.com/sqlite/foreign_keys/foreign_keys.php

[ngjaying]

@jinzhu This works but it creates foreign key without constraints. How can I set constraints to restricted for update/delete by tags?

[jinzhu]

@jinzhu This works but it creates foreign key without constraints. How can I set constraints to restricted for update/delete by tags?

GORM V2 should create foreign keys.