外键约束在用Rails保存新记录时失败

Question

我试图用Rails保存一个新记录到SQLite，但到目前为止还没有成功。

我有一个"章节"的表，用一列表示"sectioncode"，它由另一个名为"空间"的表引用，作为"sectioncode_id !"section与空格有has_many关系。

:

Section(id: integer, sectioncode: integer, name: string, description: text, created_at: datetime, updated_at: datetime)

空间:

Space(id: integer, spacecode: integer, name: string, description: text, sectioncode_id: integer, created_at: datetime, updated_at: datetime)

(其中sectioncode_id引用section的sectioncode列)

在Section.rb:

has_many :spaces, foreign_key: :sectioncode_id, primary_key: :sectioncode, dependent: :destroy

在Space.rb:

belongs_to :section, foreign_key: :sectioncode_id, primary_key: :sectioncode, required: true

在创建空间的迁移文件中:

t.references :sectioncode, foreign_key: {to_table: :sections}

当我尝试将新记录保存到空格时，Rails显示SQLite3::ConstraintException: FOREIGN KEY constraint failed (ActiveRecord::InvalidForeignKey)。

TL;DR: Rails报告一个无效的外键，但SQLite本身没有。

我试着看看我是否可以通过SQLite控制台手动创建记录，我能够，没有任何错误。当我这样做时，Rails正确地处理了我执行的所有其他操作的关系。我使用了与Rails报告的SQLite相同的命令

从Rails:

TRANSACTION (0.1ms)  begin transaction
Space Exists? (0.2ms)  SELECT 1 AS one FROM "spaces" WHERE "spaces"."spacecode" = ? AND "spaces"."sectioncode_id" = ? LIMIT ?  [["spacecode", 1], ["sectioncode_id", 1], ["LIMIT", 1]]
Space Create (0.6ms)  INSERT INTO "spaces" ("spacecode", "name", "description", "sectioncode_id", "created_at", "updated_at") VALUES (?, ?, ?, ?, ?, ?)  [["spacecode", 1], ["name", "S1"], ["description", "D1"], ["sectioncode_id", 1], ["created_at", "2023-04-02 00:16:19.836125"], ["updated_at", "2023-04-02 00:16:19.836125"]]
TRANSACTION (0.1ms)  rollback transaction              
/Users/user/.rvm/gems/ruby-3.0.0/gems/sqlite3-1.6.1-x86_64-darwin/lib/sqlite3/statement.rb:108:in `step': SQLite3::ConstraintException: FOREIGN KEY constraint failed (ActiveRecord::InvalidForeignKey)                                              
/Users/user/.rvm/gems/ruby-3.0.0/gems/sqlite3-1.6.1-x86_64-darwin/lib/sqlite3/statement.rb:108:in `step': FOREIGN KEY constraint failed (SQLite3::ConstraintException)

但是，有["sectioncode", 1]的section确实存在:

3.0.0 :013 > Section.find_by!(sectioncode: 1)
Section Load (0.1ms)  SELECT "sections".* FROM "sections" WHERE "sections"."sectioncode" = ? LIMIT ?  [["sectioncode", 1], ["LIMIT", 1]]
=>                                                            
#<Section:0x00007ff41089a010                                   
id: 4,                                                        
sectioncode: 1,                                               
name: "Sec1",                                                 
description: "Desc1",                                         
created_at: Sun, 02 Apr 2023 00:16:12.260733000 UTC +00:00,   
updated_at: Sun, 02 Apr 2023 00:16:12.260733000 UTC +00:00>

在SQLite:

sqlite> INSERT INTO "spaces" ("spacecode", "name", "description", "sectioncode_id", "created_at", "updated_at") VALUES (1, "S1", "D1", 1, "2023-04-02 00:16:19.836125", "2023-04-02 00:16:19.836125");
sqlite> 

这成功地保存了具有正确外键关系的记录。

为什么会发生这种情况?到底哪里出了问题?

编辑:下面是我用来节省新空间的代码:

space = Space.new(spacecode: 1, name: "S1 (Seeded)", description: "D1", sectioncode_id: 1)
space.save

Answer 1

这在spaces.sectioncode_id上设置了一个引用sections.id的约束:

t.references :sectioncode, foreign_key: {to_table: :sections}

>> Section.create!(sectioncode: 100)
=> #<Section:0x00007fbfffa8a0e0 id: 1, sectioncode: 100>
>> Space.create!(spacecode: 1, sectioncode_id: 100)
SQLite3::ConstraintException: FOREIGN KEY constraint failed (ActiveRecord::InvalidForeignKey)
FOREIGN KEY constraint failed (SQLite3::ConstraintException)

因为spaces.sectioncode_id100不匹配sections.id1.

在sqlite中，您必须启用外键检查:

sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite> INSERT INTO "spaces" ("spacecode", "sectioncode_id") VALUES (1, 123);
Runtime error: FOREIGN KEY constraint failed (19)

---

修复方法如下:

create_table :sections do |t|
# you need to have a unique index
t.integer :sectioncode, index: {unique: true}
end
create_table :spaces do |t|
t.integer :spacecode
# add constraint for correct columns
t.references :sectioncode,
foreign_key: {to_table: :sections, primary_key: :sectioncode}
end

>> Section.create!(sectioncode: 100)
=> #<Section:0x00007fbfffa8a0e0 id: 1, sectioncode: 100>
>> Space.create!(spacecode: 1, sectioncode_id: 100)
=> #<Space:0x00007fbfff78ab38 id: 1, spacecode: 1, sectioncode_id: 100>

---

spacecode和sectioncode似乎没有必要，您正在复制id的角色。如果有的话，两个都应该命名为code:

# if I have a `space`
space = Space.first
space.code
# if I have a `section`
section = Section.first
section.code

这样就简单多了:

create_table :sections do |t|
t.integer :code
end
create_table :spaces do |t|
t.integer :code
t.references :section, null: false, foreign_key: true
end

class Space < ApplicationRecord
belongs_to :section
end
class Section < ApplicationRecord
has_many :spaces, dependent: :destroy
end

>> Space.create!(code: 1000, section: Section.create!(code: 100))
=> #<Space:0x00007f20613bc3a0 id: 1, code: 1000, section_id: 1>