Enforce foreign key constraints in GORM SQLite

Question

Answer: Use db.Exec("PRAGMA foreign_keys = ON") to enforce foreign key constraint checks. Thanks @outdead

When I update my SQLite database using GORM, foreign key constraints aren't enforced.

I have these 2 models:

type Cat struct {
    ID      int   
    Name    string
    Breed   string
    OwnerID int  
    Owner   Owner 
}

type Owner struct {
    ID    int   
    Name  string
    Phone string
}

Which correctly creates a foreign key constraint where owner_id references id in owners. This can be verified by running: .schema cats in the SQLite shell:

CREATE TABLE `cats` (`id` integer,`name` text,`breed` text,`owner_id` integer,PRIMARY KEY (`id`),CONSTRAINT `fk_cats_owner` FOREIGN KEY (`owner_id`) REFERENCES `owners`(`id`));

I have tried PRAGMA foreign_keys = ON; which enforces foreign keys when I run commands in the SQLite shell. If I try to update an owner_id to an id that doesn't exist in owners, I get: Error: FOREIGN KEY constraint failed, which is the behaviour that I want, however, GORM is still able to execute these updates without receiving this error.

Every new connection to a sqlite database needs to explicitly turn on foreign key enforcement for that connection. It's not a one and done thing. — Shawn, Dec 01 '21 at 23:11

outdead · Accepted Answer · 2021-12-02T00:05:10.693

7

You need to exec query to turn on PRAGMA foreign_keys before updating

if res := db.Exec("PRAGMA foreign_keys = ON", nil); res.Error != nil {
    return res.Error
}

edited Dec 02 '21 at 00:05

answered Dec 01 '21 at 23:31

outdead

448
6
15

2

Ah thank you so much. It was the `db.Exec("PRAGMA foreign_keys = ON", nil)` that fixed the issue for me. The `gorm` struct tags are not required as the foreign key constraints are created by Gorm by simply referencing `Owner` as type `Owner` – barjo Dec 01 '21 at 23:52
1

Yes, you're right, you don't need to define a tag, you use a simple linkage. My fault. – outdead Dec 02 '21 at 00:04

score 1 · Answer 2 · answered Feb 16 '23 at 11:55

An alternative to the other answer is to append ?_foreign_keys=on to the connection string:

db, err := gorm.Open(sqlite.Open("my.db?_foreign_keys=on"), &gorm.Config{})

See the go-sqlite3 driver and this question.

Verified working with gorm v1.23.1, gorm's sqlite driver v1.3.1, and go-sqlite3 v2.0.3.

Enforce foreign key constraints in GORM SQLite

2 Answers2