英文:
Enforce foreign key constraints in GORM SQLite
问题
使用db.Exec("PRAGMA foreign_keys = ON")来强制执行外键约束检查。感谢 @outdead
当我使用GORM更新我的SQLite数据库时，外键约束没有被执行。
我有以下两个模型：
type Cat struct {
ID int
Name string
Breed string
OwnerID int
Owner Owner
}
type Owner struct {
ID int
Name string
Phone string
}
这样可以正确地创建一个外键约束，其中owner_id引用了owners中的id。可以通过在SQLite shell中运行.schema cats来验证：
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`));
我尝试过PRAGMA foreign_keys = ON;，它在我在SQLite shell中运行命令时强制执行外键。如果我尝试将owner_id更新为owners中不存在的id，我会得到Error: FOREIGN KEY constraint failed，这是我想要的行为。然而，GORM仍然能够执行这些更新而不会收到此错误。
英文:
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.
答案1
得分: 7
你需要执行查询来打开PRAGMA foreign_keys，然后再进行更新操作。
if res := db.Exec("PRAGMA foreign_keys = ON", nil); res.Error != nil {
return res.Error
}
英文:
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
}
答案2
得分: 1
另一种方法是在连接字符串中添加?_foreign_keys=on：
db, err := gorm.Open(sqlite.Open("my.db?_foreign_keys=on"), &gorm.Config{})
请参考go-sqlite3驱动和这个问题。
已验证在gorm v1.23.1、gorm的sqlite驱动v1.3.1和go-sqlite3 v2.0.3上可正常工作。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论