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.

Deneb

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. – Pendergrass

Answer 1

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
}

Ignaciaignacio

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 – Deneb

Yes, you're right, you don't need to define a tag, you use a simple linkage. My fault. – Ignaciaignacio

Answer 2

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.

Dianoia