Description
A foreign key constraint that's classified as deferred (see SQLite docs here) breaks the transaction state.
Example program:
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", "file::memory:")
db.Exec(`PRAGMA foreign_keys=ON`)
db.Exec(`CREATE TABLE "pk_table" ("id" integer not null primary key autoincrement)`)
db.Exec(`CREATE TABLE "fk_table" ("fk" integer not null default 0 REFERENCES pk_table(id) DEFERRABLE INITIALLY DEFERRED)`)
tx, _ := db.Begin()
tx.Exec(`INSERT INTO "fk_table" (fk) VALUES (0)`)
tx.Commit()
tx, err := db.Begin()
fmt.Println(err.Error())
}
For a more profound example with proper error checking:
fk_constraint_transaction.go.txt
According to the semantics of database/sql, a transaction must be finished after a call to tx.Commit().
However, according to SQLite documentation of deferred foreign key constraints:
Deferred foreign key constraints are not checked until the transaction tries to COMMIT. For as long as the user has an open transaction, the database is allowed to exist in a state that violates any number of deferred foreign key constraints. However, COMMIT will fail as long as foreign key constraints remain in violation.
A very similar issue was reported here: #184