MySQL Laravel – 给已有表添加包含数据的外键

在开发过程中，我们可能需要给已有的数据表添加外键。这个过程相对简单，但是，如果这个表中已有大量的数据，就会变得复杂，因为外键要求参照目标必须存在，因此我们需要确保所有的参照目标在表中存在。下面，我们将介绍如何在已有的表中添加外键，并提供相关示例。

阅读更多：MySQL 教程

关于MySQL中的外键

在MySQL中，外键被用来维护表之间的引用关系。插入或更新数据时，MySQL会检查该数据是否属于外键表中指定的关联数据。如果该数据不在关联数据中，MySQL则会阻止该操作。

当创建表时定义外键，MySQL会自动为外键创建索引。索引将加速查询和维护表之间的引用关系。在已有的表中添加外键，我们也可以指定索引，以加速查询。

创建外键

在MySQL中，外键的创建需要满足两个条件：第一，外键列和引用列应该具有相同的数据类型；第二，外键列和引用列应该具有相同的大小。创建外键的语法如下：

ALTER TABLE 表名 ADD FOREIGN KEY (外键列名) REFERENCES 目标表名(目标列名) [ON DELETE|UPDATE CASCADE|SET NULL|RESTRICT];

在代码中，我们需要使用Laravel的DB Facade来执行MySQL查询，如下所示：

use Illuminate\Support\Facades\DB;

DB::statement('ALTER TABLE 表名 ADD FOREIGN KEY (外键列名) REFERENCES 目标表名(目标列名) [ON DELETE|UPDATE CASCADE|SET NULL|RESTRICT];');

其中，on delete和on update子句用于指定参照目标删除或者更新时要执行的操作，常见的操作有：CASCADE、SET NULL、RESTRICT。CASCADE表示当参照目标执行删除或者更新时，外键列和引用列应该相应地执行相同的操作。例如：

CASCADE：删除或更新参照目标，外键列和引用列也会相应地删除或更新。
SET NULL：删除或更新参照目标时，外键列被设置为NULL。
RESTRICT：阻止参照目标的删除或更新。

给已有表添加外键

在Laravel中，我们可以使用DB Facade在已有的表中添加外键。添加外键需要遵循以下步骤：

1. 更新旧数据以引入外键参照目标

在添加外键之前，我们必须更新表中的数据，以确定所有的外键参照目标在表中都存在。此时，如果我们插入了无效的数据，MySQL会拒绝该操作并返回错误。因此，在执行添加外键操作之前，我们需要确保所有的参照目标都在表中存在。

2. 添加外键

在更新完旧数据之后，我们可以添加外键并创建引用表所需的相应索引：

use Illuminate\Support\Facades\DB;

DB::statement('ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键列名) REFERENCES 目标表名(目标列名)');

为了增加查询性能，我们可以在外键列和目标列上创建索引，如下所示：

use Illuminate\Support\Facades\DB;

DB::statement('CREATE INDEX 索引名 ON 表名(外键列名);');
DB::statement('CREATE INDEX 索引名 ON 目标表名(目标列名);');

3. 测试外键

在添加外键之后，我们应该进行测试以确保所有的参照目标在表中存在，并且所有的引用关系都已建立。很显然，测试时所需的数据量和测试条件可能会有所不同。我们可以利用Laravel的测试框架进行测试。下面是一个简单的测试用例，演示了如何执行基本的外键测试：

use Illuminate\Foundation\Testing\DatabaseTransactions;
use Illuminate\Support\Facades\DB;
use Tests\TestCase;

class ForeignKeyTest extends TestCase
{
    use DatabaseTransactions;

    /**
     * 测试外键是否能够插入无效值
     */
    public function testInsertInvalidData()
    {
        this->expectException(PDOException::class);

        // 先插入一个参照目标
        DB::table('目标表名')->insert([
            '目标列名' => 'test'
        ]);

        // 再插入一个无效值
        DB::table('表名')->insert([
            '外键列名' => 'invalid',
            // 目标表已经插入了'test'，因此这应该是一个无效的参照目标
        ]);
    }

    /**
     * 测试外键是否能够删除参照目标
     */
    public function testDeleteReference()
    {
        // 插入一个参照目标id = DB::table('目标表名')->insertGetId([
            '目标列名' => 'test'
        ]);

        // 插入一个有效的值
        DB::table('表名')->insert([
            '外键列名' => id,
            // 目标表已经插入了'test'，因此这应该是一个有效的参照目标
        ]);

        // 删除参照目标
        DB::table('目标表名')->delete(id);

        // 验证是否删除成功
        this->assertDatabasehas('表名', [
            '外键列名' =>id,
        ]);
    }

    /**
     * 测试外键是否能够更新参照目标
     */
    public function testUpdateReference()
    {
        // 插入两个参照目标
        id1 = DB::table('目标表名')->insertGetId([
            '目标列名' => 'test1'
        ]);id2 = DB::table('目标表名')->insertGetId([
            '目标列名' => 'test2'
        ]);

        // 先把一个有效值插入
        DB::table('表名')->insert([
            '外键列名' => id1,
            // 目标表已经插入了'test1'，因此这应该是一个有效的参照目标
        ]);

        // 把参照目标更新为另外一个
        DB::table('表名')->where('外键列名',id1)->update([
            '外键列名' => id2,
            // 目标表已经插入了'test2'，因此这应该是一个有效的参照目标
        ]);

        // 验证是否更新成功this->assertDatabasehas('表名', [
            '外键列名' => $id2,
        ]);
    }
}

在测试用例中，我们可以测试外键是否能够插入无效值、是否能够删除参照对象以及是否能够更新参照目标等。

总结

在本文中，我们介绍了如何在MySQL Laravel中为已有表添加包含数据的外键。在添加外键之前，我们必须先更新原有数据，以确定所有的引用目标均在表中存在。我们使用Laravel的DB Facade创建外键和索引。最后，我们利用Laravel的测试框架对新添加的外键进行了测试，以确保其能够正常运行。