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

Question

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

迁移文件

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”整数 NOT NULL

Answer 1

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

从头开始添加表时，您可以指定 NOT NULL，这就是您使用“:null => false”符号所做的事情。但是，添加列时不能执行此操作。 SQLite 的规范规定您必须为此设置一个默认值，这是一个糟糕的选择。添加默认值不是一个选项，因为它违背了使用 NOT NULL 外键的目的 - 即数据完整性。

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

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

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

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

Answer 2

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

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

请参阅：

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

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

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

Answer 3

您可以添加具有默认值的列：

ALTER TABLE table1 ADD COLUMN userId INTEGER NOT NULL DEFAULT 1

Answer 4

如果您的表包含现有行，那么您需要在添加 null 约束之前更新现有行。 迁移指南建议使用本地模型，如下所示：

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

轨道 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

Answer 5

以下迁移在 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

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

change_column_null 的 API 文档

Answer 6

不要忘记，要求使用 ALTER TABLE ADD COLUMN NOT NULL 的默认值也有一些积极的作用，至少在将列添加到包含现有数据的表中时是这样。如 https://www.sqlite.org/lang_altertable.html#altertabaddcol 中所述:

ALTER TABLE 命令通过修改模式的 SQL 文本来工作
存储在 sqlite_schema 表中。没有对表进行任何更改
用于重命名或添加列的内容。正因为如此，执行
此类 ALTER TABLE 命令的时间与数据量无关
在表中。它们在包含 1000 万行的表上运行速度与
在有 1 行的桌子上。

文件格式本身支持此 https://www.sqlite.org/fileformat.html

一条记录的值可能少于该记录中的列数
对应表。例如，在 ALTER 之后，可能会发生这种情况
TABLE ... ADD COLUMN SQL 语句增加了列数
在表架构中，而不修改表中预先存在的行。
记录末尾的缺失值使用
表中定义的相应列的默认值
架构。

使用此技巧，可以通过仅更新架构来添加新列，该操作在包含 670 万行的测试表中花费了 387 毫秒。数据区中的现有记录根本不被触及，节省的时间是巨大的。添加列的缺失值来自架构，如果没有另外说明，默认值为 NULL。如果新列不为 NULL，则必须将默认值设置为其他值。

我不知道为什么当表为空时没有 ALTER TABLE ADD COLUMN NOT NULL 的特殊路径。一个好的解决方法也许是从一开始就创建表。