无法在Sqlite3中添加具有默认值NULL的NOT NULL列 [英] Cannot add a NOT NULL column with default value NULL in Sqlite3

问题描述

在尝试将NOT NULL列添加到现有表时出现以下错误.为什么会发生?我尝试过rake db:reset，以为现有记录是问题所在，但是即使在重置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:无法添加具有默认值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.

请参阅:

• 添加非空列到现有表格(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)

该博客的建议是添加不具有非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.

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