Ruby-on-rails 如何解决SQLite3中的“无法添加默认值为NULL的NOT NULL列”？

How to solve "Cannot add a NOT NULL column with default value NULL" in SQLite3?

提问by felix

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.

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

Migration File

迁移文件

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

Error Message

错误信息

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

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

采纳答案by Bill Karwin

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.

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

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.

SQLite 通常会选择 NULL，但您已指定它不能为 NULL，那么它应该是什么？它没有办法知道。

See:

看：

• 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)

• 在 Rails 迁移中添加没有默认值的非空列（2009 年，不再可用，因此这是 archive.org 上的快照）
• 将 NOT NULL 列添加到现有表(2014)

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_idand then use change_columnto add the not null constraint.

该博客的建议是添加没有非空约束的列，并且在每一行中添加NULL。然后您可以在 中填写值division_id，然后用于change_column添加非空约束。

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

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

回答by Jaime Bellmyer

This is (what I would consider) a glitch with SQLite. This error occurs whether there are any records in the table or not.

这是（我会考虑的）SQLite 的一个小故障。无论表中是否有任何记录，都会发生此错误。

When adding a table from scratch, you can specify NOT NULL, which is what you're doing with the ":null => false" notation. However, you can't do this when adding a column. SQLite's specification says you have to have a default for this, which is a poor choice. Adding a default value is not an option because it defeats the purpose of having a NOT NULL foreign key - namely, data integrity.

从头开始添加表时，您可以指定 NOT NULL，这就是您使用 ":null => false" 表示法所做的。但是，在添加列时不能这样做。SQLite 的规范说你必须为此设置一个默认值，这是一个糟糕的选择。添加默认值不是一个选项，因为它违背了拥有 NOT NULL 外键的目的 - 即数据完整性。

Here's a way to get around this glitch, and you can do it all in the same migration. NOTE: this is for the case where you don't already have records in the database.

这是解决此故障的一种方法，您可以在同一次迁移中完成所有操作。注意：这是针对您在数据库中还没有记录的情况。

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

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

We're adding the column without the NOT NULL constraint, then immediately altering the column to add the constraint. We can do this because while SQLite is apparently very concerned during a column add, it's not so picky with column changes. This is a clear design smell in my book.

我们正在添加没有 NOT NULL 约束的列，然后立即更改该列以添加约束。我们可以这样做，因为虽然 SQLite 在列添加期间显然非常关注，但它对列更改并不那么挑剔。这是我书中明显的设计气味。

It's definitely a hack, but it's shorter than multiple migrations and it will still work with more robust SQL databases in your production environment.

这绝对是一个 hack，但它比多次迁移要短，并且它仍然可以在您的生产环境中使用更强大的 SQL 数据库。

回答by JosephL

If you have a table with existing rows then you will need to update the existing rows before adding your nullconstraint. The Guide on migrationsrecommends using a local model, like so:

如果您有一个包含现有行的表，那么您需要在添加null约束之前更新现有行。在上迁移指南推荐使用局部模型，如下所示：

Rails 4 and up:

Rails 4 及更高版本：

class AddDivisionIdToProfile < ActiveRecord::Migration
  class Profile < ActiveRecord::Base
  end

  def change
    add_column :profiles, :division_id, :integer

    Profile.reset_column_information
    reversible do |dir|
      dir.up { Profile.update_all division_id: Division.first.id }
    end

    change_column :profiles, :division_id, :integer, :null => false
  end

end

Rails 3

导轨 3

class AddDivisionIdToProfile < ActiveRecord::Migration
  class Profile < ActiveRecord::Base
  end

  def change
    add_column :profiles, :division_id, :integer

    Profile.reset_column_information
    Profile.all.each do |profile|
      profile.update_attributes!(:division_id => Division.first.id)
    end

    change_column :profiles, :division_id, :integer, :null => false
  end

end

回答by deltatango

The following migration worked for me in Rails 6:

以下迁移在 Rails 6 中对我有用：

class AddDivisionToProfile < ActiveRecord::Migration[6.0]
  def change
    add_reference :profiles, :division, foreign_key: true
    change_column_null :profiles, :division_id, false
  end
end

Note :divisionin the first line and :division_idin the second

注意:division第一行和:division_id第二行

API Doc for change_column_null

API 文档 change_column_null