无法在 Sqlite3 中添加默认值为 NULL 的 NOT NULL 列

2021-12-02 00:00:00 database ruby-on-rails sqlite sqlite3-ruby

我在尝试向现有表添加 NOT NULL 列时收到以下错误.为什么会这样?.我试过 rake db:reset 认为现有记录是问题所在，但即使在重置数据库后，问题仍然存在.你能帮我解决这个问题吗.

I am getting the following error while trying to add a NOT NULL column to an existing table. Why is it happening ?. I tried rake db:reset thinking that the existing records are the problem, but even after resetting the DB, the problem persists. Can you please help me figure this out.

迁移文件

class AddDivisionIdToProfile < ActiveRecord::Migration
  def self.up
    add_column :profiles, :division_id, :integer, :null => false
  end

  def self.down
    remove_column :profiles, :division_id
  end
end

错误信息

SQLite3::SQLException: 无法添加默认值为 NULL 的 NOT NULL 列:ALTER TABLE "profiles" ADD "division_id" integer NOT NULL

SQLite3::SQLException: Cannot add a NOT NULL column with default value NULL: ALTER TABLE "profiles" ADD "division_id" integer NOT NULL

推荐答案

表中已有行，并且要添加新列 division_id.它需要在每个现有行的新列中添加一些内容.

You already have rows in the table, and you're adding a new column division_id. It needs something in that new column in each of the existing rows.

SQLite 通常会选择 NULL，但您已指定它不能为 NULL，那么它应该是什么?它无从得知.

SQLite would typically choose NULL, but you've specified it can't be NULL, so what should it be? It has no way of knowing.

见:

  • 在 Rails 迁移中添加没有默认值的非空列(2009 年，不再可用，因此这是存档快照.组织)
  • 添加非空列到现有表(2014)
  • Adding a Non-null Column with no Default Value in a Rails Migration (2009, no longer available, so this is a snapshot at archive.org)
  • Adding a NOT NULL Column to an Existing Table (2014)

那个博客的建议是添加没有not null约束的列，每行都会添加NULL.然后你可以在division_id中填入值，然后使用change_column来添加not null约束.

That blog's recommendation is to add the column without the not null constraint, and it'll be added with NULL in every row. Then you can fill in values in the division_id and then use change_column to add the not null constraint.

有关执行此三步过程的迁移脚本的说明，请参阅我链接到的博客.

See the blogs I linked to for an description of a migration script that does this three-step process.

相关文章