如何解决“无法添加具有默认值NULL的NOT NULL列”在SQLite3？ [英] How to solve "Cannot add a NOT NULL column with default value NULL" in SQLite3?

问题描述

我在尝试向现有表中添加NOT NULL列时收到以下错误。为什么会发生？我试过rake db：重置认为现有的记录是问题，但即使在重置数据库后，问题仍然存在。

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：无法添加具有默认值的NOT NULL列NULL：ALTER TABLEprofilesADDdivision_idinteger 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迁移中添加没有默认值的非空列

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

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 blog I linked to for an example of a migration script that does this three-step process.

这篇关于如何解决“无法添加具有默认值NULL的NOT NULL列”在SQLite3？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！