Ecto迁移中SQLite修改非空列失败及表重命名约束问题

SQLite本身不支持ALTER COLUMN修改列的NULL约束，你之前尝试的PRAGMA设置无效，核心原因是Ecto的每个execute调用可能会使用不同的数据库连接，而PRAGMA是会话级配置，仅对当前连接生效，分开执行无法保留设置。

修正后的迁移代码 #

将所有操作包裹在同一个事务中，确保所有PRAGMA配置和表操作在同一个数据库连接会话内执行：

defmodule Oas.Repo.Migrations.NullEmail do
  use Ecto.Migration

  def up do
    repo().transaction(fn ->
      # 在同一个连接中禁用外键检查并开启legacy_alter_table
      repo().query!("PRAGMA foreign_keys = OFF")
      repo().query!("PRAGMA legacy_alter_table = ON")

      # 重命名原表为临时表
      rename table(:members), to: table(:members_tmp)

      # 创建新表，仅修改email列的null约束
      create table(:members) do
        add :email, :string, null: true, collate: :nocase
        add :name, :string, null: false
        timestamps()
      end

      # 复制数据（明确指定列名避免顺序问题）
      repo().query!("""
        INSERT INTO members (email, name, inserted_at, updated_at)
        SELECT email, name, inserted_at, updated_at FROM members_tmp
      """)

      # 删除临时表
      drop table(:members_tmp)

      # 恢复外键检查和默认配置
      repo().query!("PRAGMA foreign_keys = ON")
      repo().query!("PRAGMA legacy_alter_table = OFF")
    end)
  end

  def down do
    repo().transaction(fn ->
      repo().query!("PRAGMA foreign_keys = OFF")
      repo().query!("PRAGMA legacy_alter_table = ON")

      rename table(:members), to: table(:members_tmp)

      # 回滚到原表结构（email列非空）
      create table(:members) do
        add :email, :string, null: false, collate: :nocase
        add :name, :string, null: false
        timestamps()
      end

      repo().query!("""
        INSERT INTO members (email, name, inserted_at, updated_at)
        SELECT email, name, inserted_at, updated_at FROM members_tmp
      """)

      drop table(:members_tmp)

      repo().query!("PRAGMA foreign_keys = ON")
      repo().query!("PRAGMA legacy_alter_table = OFF")
    end)
  end
end

关键注意事项 #

1. legacy_alter_table的作用：开启后，SQLite的ALTER TABLE RENAME不会自动更新外键引用，避免临时表的外键关联被错误修改。
2. 明确指定列名复制数据：避免因原表和新表列顺序不一致导致数据映射错误，比SELECT *更安全。
3. 事务原子性：所有操作在同一个事务中执行，确保中途失败时数据库自动回滚到初始状态，避免数据不一致。
4. 同步索引与约束：如果原表有额外索引或约束（如唯一键），需要在创建新表后手动添加对应配置。

内容的提问来源于stack exchange，提问作者user518184