Enforce foreign key constraints in GORM SQLite-CodePudding

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.

CodePudding user response：

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
}