Foreign key constraint just doesn't work

[nullchinchilla]

I am probably misunderstanding something, but it appears that foreign key constraints are ignored no matter what I do. Here's an example:

package main

import (
	"database/sql"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db, _ := sql.Open("sqlite3", "test.db")
	tx, _ := db.Begin()
	defer tx.Commit()
	tx.Exec("PRAGMA foreign_keys = ON")
	tx.Exec("create table foo (a)")
	tx.Exec("create table bar (a references foo(a))")
	_, err := tx.Exec("insert into bar values ('lol')")
	if err != nil {
		panic(err.Error())
	}
}

Expected result: panic. Actual result: test.db gets created, with the values being inconsistent with the foreign key constraint.

Why is this happening? I even recall using this package before with foreign keys without issue...

[nullchinchilla]

In fact, it seems that all PRAGMAs cannot be changed within a transaction. For example, changing the journal_mode also works only with db.Exec and not tx.Exec. If PRAGMA foreign_keys = ON is moved outside the transaction, the program successfully crashes.

Of course, for foreign_keys you have to do it for every transaction...

[gwenn]

The documentation says:

This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

With SQLite shell :

sqlite> begin;
sqlite> PRAGMA foreign_keys = ON;
sqlite> create table foo (a);
sqlite> create table bar (a references foo(a));
sqlite> insert into bar values ('lol');
sqlite> commit;
sqlite> pragma foreign_key_check;
Error: foreign key mismatch - "bar" referencing "foo"

[nullchinchilla]

I know. But you have to do PRAGMA foreign_keys = ON per connection, but the sql package doesn't expose such an abstraction.

This package used to allow opening files with ?foreign_keys=on to run the pragma per connection, but it seems like the code to handle that has been removed.

[gwenn]

Maybe a connection hook will do the job ?

[nullchinchilla]

That's indeed what I eventually used. However, it would be nice for such a basic feature to be more easily accessible. The old foreign_keys=on thing was great, but I do not know why it was removed.

[gwenn]

Are you sure it was removed ?

$ git log -p sqlite3.go | grep 'foreign_keys'
-- no match

[nullchinchilla]

#255 (comment) That line no longer exists, and foreign_keys no longer works when I try to use it.

[gwenn]

Sorry for the confusion.
The #255 comment was only a suggestion for a patch (which has been ignored)...

[nullchinchilla]

Perhaps it should be added and documented then? Having to use a connection hook to do things as basic as turning on foreign keys support is rather annoying.

[mattn]

Well, I'm thinking it's possible to do with hook. But if many people want this feature, I'll add this.

[maharasheed]

So now to enforce foreign key constrains, we can only do this:
db, err := sql.Open("sqlite3", "my.db?foreign_keys=on")

[nbedos]

@maharasheed The connection parameter is _foreign_keys (with a leading underscore).

db, err := sql.Open("sqlite3", "my.db?_foreign_keys=on")

[ManishaDutt01]

I have tried everything but still i am getting foreign key error . There are my two tables
CREATE TABLE IF NOT EXISTS bag (owner CHAR(32) PRIMARY KEY, uploader CHAR(32) NOT NULL UNIQUE, downloader CHAR(32) NOT NULL UNIQUE)
"CREATE TABLE IF NOT EXISTS uploader_session(uploader CHAR(32) NOT NULL,session_id CHAR(32) NOT NULL primary key,start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY fk_uploader_session (uploader) REFERENCES bag(uploader) )"

error: err:"near "fk_uploader_session": syntax error"

[rittneje]

@ManishaDutt01 Your syntax is invalid. See https://www.sqlite.org/lang_createtable.html It would be CONSTRAINT fk_uploader_session FOREIGN KEY (uploader) REFERENCES bag(uploader).

[ManishaDutt01]

@rittneje Thanks understood