使用 SQLite 进行 Laravel 迁移“无法添加默认值为 NULL 的 NOT NULL 列"

为什么在使用 SQLite 驱动程序时会收到此警告?我的 MySQL 驱动程序没有问题，但 SQLite 抛出此错误.

这对我来说没有意义，因为我知道播种是在所有迁移完成后发生的，所以为什么它会抱怨这个问题，只有在数据库中已经存在数据时才会出现这个问题.

我的两次迁移是

第一次迁移

 公共函数 up() {Schema::create('users', function($table) {$table->increments('id');$table->string('用户名');$table->string('email');$table->string('密码');});}

第二次迁移

public function up() {Schema::table('users', function(Blueprint $table) {$table->date('生日')->after('id');$table->string('last_name')->after('id');$table->string('first_name')->after('id');});}

错误

异常:SQLSTATE[HY000]:一般错误:1 无法添加默认值为 NULL 的 NOT NULL 列(SQL:alter table "users" add column "birthday" date not null)

解决方案

这看起来像是 SQLite 的一个奇怪之处.根据 关于同一问题的 Laracast 论坛帖子:

<块引用><块引用>

从头开始添加表时，您可以指定 NOT NULL.但是，在添加列时不能这样做.SQLite 的规范说你必须为此设置一个默认值，这是一个糟糕的选择.

进一步查看 SQLite ALTER TABLE 文档，我发现:

<块引用><块引用>

如果指定了 NOT NULL 约束，则该列必须具有非 NULL 的默认值.

我想在 SQLite 的世界中，不提供默认值与说默认值应该是 NULL 是一回事(而不是意味着这个不可为空的列没有默认值，所以一个值 必须在每个插页上提供它).

如果您需要将不可为空的列添加到现有表中，那么 SQLite 似乎只会让您处于糟糕的状态，而该列也不应具有默认值.

Why am I getting this warning when using the SQLite driver? I have no problems with the MySQL driver but SQLite is throwing this error.

It does not make sense to me since I understood the seeding happens after all the migrations are completed so why is it complaining about this issue which would only arise if data was already present in the database.

My two migrations are

FIRST MIGRATION

  public function up() {
    Schema::create('users', function($table) {
      $table->increments('id');
      $table->string('username');
      $table->string('email');
      $table->string('password');
    });
  } 

SECOND MIGRATION

public function up() {
    Schema::table('users', function(Blueprint $table) {
        $table->date('birthday')->after('id');
        $table->string('last_name')->after('id');
        $table->string('first_name')->after('id');
    });
}

ERROR

Exception: SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL (SQL: alter table "users" add column "birthday" date not null)

解决方案

It looks like this is a SQLite oddity. According to a Laracast forum thread about the same issue:

When adding a table from scratch, you can specify NOT NULL. 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.

Looking further to the SQLite ALTER TABLE docs, I found:

If a NOT NULL constraint is specified, then the column must have a default value other than NULL.

I suppose in the world of SQLite, not providing a default value is the same thing as saying the default value should be NULL (as opposed to meaning there is no default value for this non-nullable column, so a value must be provided for it on each insert).

It seems SQLite simply leaves you in a bad state if you need to add a non-nullable column to an existing table, which column should also not have a default value.