我试图用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
这在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_id
100
不匹配sections.id
1
.
在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>