克隆Heroku应用后新增迁移引发PG::NotNullViolation错误求助

错误信息 #

PG::NotNullViolation: ERROR: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, nini, momo, 2023-01-21, 2023-01-31, rwe, rwe, Upper, 18, Report_Cards_-_K-9_Three_Term.pdf, application/pdf, 847591, 2023-01-27 01:46:36.772805, ewr, werr, 449, f, f, f, T4-1007-0001_2020.pdf, application/pdf, 60955, 2023-01-27 01:46:36.803862, null, null, null, null, null, null, null, null, null, null, null, null, 2023-01-27 01:46:36.867886, 2023-01-27 01:46:36.867886). : INSERT INTO "cases" ("pt_first_name", "pt_last_name", "date_received", "due_date", "ship", "shade", "finished", "outsourced", "mould", "upper_lower", "implant_brand", "implant_quantity", "invoice_file_name", "invoice_content_type", "invoice_file_size", "invoice_updated_at", "invoice2_file_name", "invoice2_content_type", "invoice2_file_size", "invoice2_updated_at", "number", "user_id", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24) RETURNING "id"

近期操作 #

从Heroku克隆应用并拉取完整数据库后，添加了以下迁移：

def self.up
  change_table :cases do |t|
    t.attachment :invoice2
    t.attachment :invoice3
    t.attachment :invoice4
    t.attachment :invoice5
    t.timestamps
  end
end

def self.down
  remove_attachment :cases, :invoice2
  remove_attachment :cases, :invoice3
  remove_attachment :cases, :invoice4
  remove_attachment :cases, :invoice5
end

当前Schema信息 #

create_table "cases", force: :cascade do |t|
  t.string   "pt_first_name"
  t.string   "pt_last_name"
  t.date     "date_received"
  t.date     "due_date"
  t.string   "shade"
  t.string   "mould"
  t.string   "upper_lower"
  t.integer  "user_id"
  t.string   "invoice_file_name"
  t.string   "invoice_content_type"
  t.integer  "invoice_file_size",     limit: 8
  t.datetime "invoice_updated_at"
  t.string   "implant_brand"
  t.string   "implant_quantity"
  t.integer  "number"
  t.boolean  "finished"
  t.boolean  "ship"
  t.boolean  "outsourced"
  t.string   "invoice2_file_name"
  t.string   "invoice2_content_type"
  t.integer  "invoice2_file_size",    limit: 8
  t.datetime "invoice2_updated_at"
  t.string   "invoice3_file_name"
  t.string   "invoice3_content_type"
  t.integer  "invoice3_file_size",    limit: 8
  t.datetime "invoice3_updated_at"
  t.string   "invoice4_file_name"
  t.string   "invoice4_content_type"
  t.integer  "invoice4_file_size",    limit: 8
  t.datetime "invoice4_updated_at"
  t.string   "invoice5_file_name"
  t.string   "invoice5_content_type"
  t.integer  "invoice5_file_size",    limit: 8
  t.datetime "invoice5_updated_at"
  t.datetime "created_at"
  t.datetime "updated_at"
end

问题分析与解决方法 #

原因 #

错误本质是PostgreSQL的cases表id字段自增序列失效，插入新记录时无法自动生成非空id值。从Heroku导入数据库后，本地表的序列可能未正确同步，或迁移操作意外影响了序列配置。

解决步骤 #

1. 连接本地PostgreSQL数据库：
   终端执行：

   rails dbconsole
   

2. 检查序列状态：
   执行SQL查询当前最大id和序列当前值：

   SELECT MAX(id) FROM cases;
   SELECT last_value FROM cases_id_seq;
   

3. 重置序列起始值：
   如果序列last_value小于表中最大id，执行以下SQL（替换{max_id}为查询到的最大id值）：

   ALTER SEQUENCE cases_id_seq RESTART WITH {max_id} + 1;
   

4. 验证修复：
   尝试重新添加新案例，确认错误是否消失。

若以上方法无效，可尝试重新创建序列：

DROP SEQUENCE IF EXISTS cases_id_seq;
CREATE SEQUENCE cases_id_seq OWNED BY cases.id;
ALTER TABLE cases ALTER COLUMN id SET DEFAULT nextval('cases_id_seq');
ALTER SEQUENCE cases_id_seq RESTART WITH (SELECT MAX(id) + 1 FROM cases);

内容的提问来源于stack exchange，提问作者nourza