使用 SQLite 进行 Laravel 迁移“无法添加默认值为 NULL 的 NOT NULL 列”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20822159/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel migration with SQLite 'Cannot add a NOT NULL column with default value NULL'
提问by user391986
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.
为什么在使用 SQLite 驱动程序时会收到此警告？我的 MySQL 驱动程序没有问题，但 SQLite 抛出了这个错误。
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)
回答by JAAulde
It looks like this is a SQLite oddity. According to a Laracast forum thread about the same issue:
看起来这是一个 SQLite 的奇怪之处。根据关于同一问题的Laracast 论坛帖子：
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.
从头开始添加表时，您可以指定 NOT NULL。但是，在添加列时不能这样做。SQLite 的规范说你必须为此设置一个默认值，这是一个糟糕的选择。
Looking further to the SQLite ALTER TABLEdocs, I found:
进一步查看SQLiteALTER TABLE文档，我发现：
If a NOT NULL constraint is specified, then the column must have a default value other than NULL.
如果指定了 NOT NULL 约束，则该列必须具有非 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 mustbe provided for it on each insert).
我想在 SQLite 的世界中，不提供默认值与说默认值应该是 NULL 是一回事（而不是意味着这个不可为空的列没有默认值，所以必须为它提供一个值在每个插入）。
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.
如果您需要向现有表添加不可为空的列，那么 SQLite 似乎只会让您处于糟糕的状态，而该列也不应该具有默认值。
回答by Laurence
If you don't want the column to be nullable- then you need to let Laravel know what the default shouldbe.
如果您不希望该列成为nullable- 那么您需要让 Laravel 知道默认值应该是什么。
One option is an empty string ""like this
一种选择是""像这样的空字符串
public function up() {
Schema::create('users', function($table) {
$table->date('birthday')->after('id')->default('');
$table->string('last_name')->after('id')->default('');
$table->string('first_name')->after('id')->default('');
});
}
回答by StuBez
A workaround I've used successfully is to check which database driver is being used and slightly modify the migration for SQLite.
我成功使用的解决方法是检查正在使用哪个数据库驱动程序并稍微修改 SQLite 的迁移。
For example:
例如：
class MyMigration extends Migration
{
public function up()
{
$driver = Schema::connection($this->getConnection())->getConnection()->getDriverName();
Schema::table('invoices', function (Blueprint $table) use ($driver) {
$table->string('stripe_invoice')->nullable()->change();
if ('sqlite' === $driver) {
$table->string('stripe_invoice_number')->default('');
} else {
$table->string('stripe_invoice_number')->after('stripe_invoice');
}
});
}
}
回答by PositiveLogic
I'm not familiar with Laravel, but apparently the use of the aftermethod to specify the order of columns appears to specifically mention ONLYMySQL(Laravel)and a discussion (GitHub)seems to point to difficulties in it's use with SQLite. It may likely be incompatible because its function: "...use the after method to specify the order of columns" design runs up against the limitation in SQLite's documentation (SQLite)for adding columns... which reads: "The new column is always appended to the end of the list of existing columns."I can't say whether assigning default values with ->default($value)can get you by or not.
我对 Laravel 不熟悉，但显然使用该after方法来指定列的顺序似乎专门提到了ONLY MySQL(Laravel)并且讨论(GitHub)似乎指出了它与 SQLite 一起使用的困难。它有可能存在不兼容，因为它的功能：“......方法后使用to specify the order of columns”的设计遭遇了SQLite的文档中的限制（SQLite的）添加列...曰："The new column is always appended to the end of the list of existing columns."我不能说是否有指定的默认值->default($value)即可得到你与否。
回答by elliotanderson
you have to add
你必须添加
->nullable()
for the columns that may have a null value
对于可能具有空值的列
回答by shxfee
Another work around for this issue is to first create the fields as nullable and then later change the fields to be not null. So for example in this case we will do something like the following:
此问题的另一个解决方法是首先将字段创建为可为空，然后将字段更改为非空。因此，例如在这种情况下，我们将执行以下操作：
public function up() {
// Create fields first as nullable
Schema::table('users', function(Blueprint $table) {
$table->date('birthday')->after('id')->nullable();
$table->string('last_name')->after('id')->nullable();
$table->string('first_name')->after('id')->nullable();
});
// Either truncate existing records or assign a value to new fields
if (true) {
DB::table('users')->truncate();
} else {
DB::table('users')->update([
'birthday' => '2019-05-01',
'last_name' => 'last name',
'first_name' => 'first name',
]);
}
// Change the fields to not be null
Schema::table('users', function(Blueprint $table) {
$table->date('birthday')->nullable(false)->change();
$table->string('last_name')->nullable(false)->change();
$table->string('first_name')->nullable(false)->change();
});
}
