php - 如何将带有外键的 NOT NULLABLE 列添加到已包含数据的现有表中？

Question

这个问题的一个例子:

Imagine you have a table projects. The table projects already contains data. Now you want to add a project_type_id column to the table, that can't be NULL and has a foreign key to the project_types table.

The database sees the project_type_id can't be NULL, so it inserts a 0 in the column for all the existing rows. The 0 does not exist in the project_types table, so the foreign key constraint fails. This means you can't add the foreign key.

我考虑过将第一个“默认”行添加到 project_types 表并将所有项目链接到该类型。这不是一个好的解决方案，因为用户必须将所有类型从“默认”更改为正确的类型。

你对这个问题的解决方案是什么？

Answer 1

我认为解决这个问题最好的方法是:

1. Create a new migration in order to add project_type_id column to projects table:

php artisan make:migration add_project_type_column_to_projects_table --table=projects

 public function up()
{
    Schema::table('projects', function (Blueprint $table) {
        $table->integer("project_type_id")->unsigned();
    });
}

public function down()
{
    Schema::table('projects', function (Blueprint $table) {
        $table->dropColumn("project_type_id");
    });
}

它会在每列中添加 0 作为默认值，但没问题，因为它还不是外键。

2.Define the relationship between Project and ProjectType models

//Your Project class

class Project extends Model
{
 protected $fillable=["your columns..."];

 public function type()
  {
    return $this->belongsTo(ProjectType::class);
  }
}


//Your ProjectType class

class ProjectType extends Model
{
  protected $fillable=["Your columns..."];

  public function projects()
  {
    return $this->hasMany(Project::class);
  }
}

3. Now you must go and set the correct values for project_type_id either manually or from your admin panel.

4. Now your column is ready to be set as foreign key, so go ahead and make a new migration to set the project_type_id as foreign key.

php artisan make:migration set_project_type_id_column_as_foreign_key --table=projects

public function up()
{
    Schema::table('projects', function (Blueprint $table) {
        $table->foreign("project_type_id")
            ->references("id")
            ->on("project_types")
            ->onDelete("cascade")//is up to you
            ->onUpdate("cascade");// is up to you
    });
}
 public function down()
{
    Schema::table('projects', function (Blueprint $table) {
        $table->dropForeign("projects_project_type_id_foreign"); //This one could be different
    });
}

现在您的表有一个具有正确值的新(不可为空)外键。