[Bug] SQLite3 not honoring Foreign key constraints leading to successful insertion of record with invalid foreign key about sqlite HOT 6 CLOSED

(Note: I do not use gorm, and am only familiar with SQLite and the mattn wrapper.)

In SQLite, for historical reasons whether foreign keys are enabled is a property of an individual database connection, NOT the database itself. Consequently the fact that your DB Browser shows foreign keys enabled is irrelevant.

In order to enable foreign keys on your application's database connections, you will likely have to set a parameter in the DSN. Should be sqlite.Open("file:" + dbName+".db?_fk=1"). See the mattn docs for more information.

from sqlite.

@rittneje Thanks. Sure.

I understand that foreign key in sqlite is connection specific. The screenshot was shared just to show that it worked from the browser and not from gorm. This issue is very specific to gorm and not sqlite I feel.

sqlite.Open("file:" + dbName+".db?_fk=1") according to my understanding is to be used only if you are dealing with sqlite package directly without an intermediate ORM interface. This connection string is expected to be handled by the gorm instead based on the tag provided in the struct field which is being done alrite in case of postgresql but no sqlite.

Hence this issue.

from sqlite.

I don't see any logic in the source code to manipulate the connection string. It just passes it verbatim. For Postgres it works because I don't think you can disable foreign keys anyway.

It would be nice if gorm just always enabled foreign keys, regardless of whether your schema references them. But that might be a breaking change, for the same reason that SQLite itself considered it a breaking change.

BTW, another workaround for now is to compile with the sqlite_foreign_keys tag, as that will enable foreign keys by default.

from sqlite.

It would be nice if gorm just always enabled foreign keys, regardless of whether your schema references them. But that might be a breaking change, for the same reason that SQLite itself considered it a breaking change.

I think gorm can have foreign key enforce option applicable / considered only for sqlite with some default value handling for backward compatibility, and user can explicitly set that flag during initialisation.

Anyway I am just speculating and throwing of ideas from my head.

from sqlite.

After updating gorm and gorm/sqlite I also stumbled upon this problem. Not including '?_fk=1' is only part of the problem as adding it to the example code makes no difference: create still does not return an error.

I think the other part of the problem is closely related to mattn/go-sqlite3#986 (comment). When using a version of gorm/sqlite with RETURNING support (and only inserting a single struct), gorm itself calls rows.Next() once, scans the result but does not call rows.Next() again, resulting in the missing error. The fix in sqlite3 itself (https://sqlite.org/src/info/a818ba2ed635b91e) may resolve this issue, but it's not released yet. I'll try to patch it by hand and reverify the problem.

EDIT: Manually updating mattn/go-sqlite3 with the newest prerelease amalgamation code fixes this problem. Guess we have to wait for sqlite 3.38.0. (:

Otherwise, downgrading to 1.1.6 would also be an option: this is the latest Version without RETURNING support.

from sqlite.

https://sqlite.org/foreignkeys.html

If it still doesn't work, try to run PRAGMA foreign_keys = ON, the case should be tested in github.com/go-gorm/gorm

from sqlite.