Gorm: Foreign key for SQLite doesn't work

[danihodovic]

It seems like SQLite does not support ALTER TABLE ADD CONSTRAINT as per https://sqlite.org/omitted.html and yet gorm tries to add foreign keys using this syntax.

When running this code I get the cryptic error message

db.CreateTable(&transaction{}).AddForeignKey("account_id", "accounts(id)", "RESTRICT", "RESTRICT")

(near "CONSTRAINT": syntax error)

Same error I hit in #634

[nbgo]

And some more details here: https://github.com/jinzhu/gorm/pull/360

As to me I use this vatiant:

type User struct {
    Ou          *OrganizationUnit
    OuID        sql.NullInt64 `sql:"type:integer REFERENCES organization_unit(id) ON DELETE CASCADE ON UPDATE CASCADE"`
}

[danihodovic]

@nbgo Thanks, I resorted to raw SQL for creating the table with the foreign key but that looks better.

[jinzhu]

@nbgo looks good.

[lmas]

So it's still a bug and you still have to use the example provided by @nbgo? If so, some note in the docs would had been nice..

[pankajcheema]

@nbgo Not working for me .

type User struct {
    Ou          *OrganizationUnit
    OuID        sql.NullInt64 `sql:"type:integer REFERENCES organization_unit(id) ON DELETE CASCADE ON UPDATE CASCADE"`
}

using sqlite3 and checking for indexes in sqlite3 using command .indices nodes

[rafalbraun]

For future travellers who reach this thread - by peeking into the gorm code one can find a line with a commentary:
847| // sqlite does not support ADD CONSTRAINT in ALTER TABLE

The reason for this is the fact that sqlite3 by default cannot add foreign key constraint on already created table - the only way to do this is through table definition (prettty peculiar I think). What is even funnier the official documentation mentions this fallback

my recommendation is to swich to mysql or postgresql or not to migrate schema through gorm