php - 错误 1215；不断收到此外键错误

Question

我在 stackOverflow 上看到了很多关于我的错误的问题，但出于某种原因，答案不适用于以下内容:

我有如下三个表:

public function up()
{
    Schema::create('activities_nl', function(Blueprint $table){

        $table->increments('id');
        $table->integer('activity_id')->unique();
        $table->string('title');
        $table->string('location');
        $table->string('price');
        $table->string('age_indication')->nullable();
        $table->longText('registration')->nullable();
        $table->longText('mandatory')->nullable();
        $table->longText('description')->nullable();
        $table->string('special_title')->nullable();
        $table->string('academy')->nullable();
        $table->timestamps();

        $table->integer('en_table')->nullable();
        $table->integer('de_table')->nullable();
    });
}

public function up()
{
    Schema::create('activities_de', function(Blueprint $table){

        $table->increments('id');
        $table->integer('activity_id')->unique();
        $table->string('title');
        $table->string('location');
        $table->string('age_indication')->nullable();
        $table->string('price');
        $table->longText('registration')->nullable();
        $table->longText('mandatory')->nullable();
        $table->longText('description')->nullable();
        $table->string('special_title')->nullable();
        $table->string('academy')->nullable();
        $table->timestamps();

        $table->integer('nl_table')->nullable();
        $table->integer('en_table')->nullable();
    });
}

public function up()
{
    Schema::create('activities_en', function(Blueprint $table){

        $table->increments('id');
        $table->integer('activity_id')->unique();
        $table->string('title');
        $table->string('location');
        $table->string('age_indication')->nullable();
        $table->string('price');
        $table->longText('registration')->nullable();
        $table->longText('mandatory')->nullable();
        $table->longText('description')->nullable();
        $table->string('special_title')->nullable();
        $table->string('academy')->nullable();
        $table->timestamps();

        $table->integer('nl_table')->nullable();
        $table->integer('de_table')->nullable();
    });
}

我尝试连接它们不是通过 increments('id') 而是通过 integer('activity_id) 。下面是连接外键的迁移。

public function up()
{
    Schema::table('activities_nl', function(Blueprint $table){

        $table->foreign('en_table')->references('activity_id')->on('activities_en');
        $table->foreign('de_table')->references('activity_id')->on('activities_de');
    });

    Schema::table('activities_de', function(Blueprint $table){

        $table->foreign('en_table')->references('activity_id')->on('activities_en');
        $table->foreign('nl_table')->references('activity_id')->on('activities_nl');
    });

    Schema::table('activities_en', function(Blueprint $table){

        $table->foreign('nl_table')->references('activity_id')->on('activities_nl');
        $table->foreign('de_table')->references('activity_id')->on('activities_de');
    });
}

出于某种原因，我一直收到外键约束。是因为 Laravel 不喜欢我尝试引用不同的 ID 吗？如果是这样，是否有解决方法..

或者这只是我忽略的拼写错误？

编辑:对于所有应该是外部的列，从 unsignedInteger 更改为仅整数。编辑:将 ->nullable() 添加到 activity_id 以便它与其他列完全相同

编辑:感谢@shadow，我发现activity_id 上没有索引。我所要做的就是让它独一无二。

Answer 1

问题是 activity_id 被定义为整数，而保存外键的字段被定义为无符号整数。这是类型不匹配。所有字段都应定义为整数或无符号整数。

请参阅 foreign keys 上的 mysql 文档:

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

此外，您需要为要设置的外键在引用字段和被引用字段上创建索引。如果引用字段不存在，Mysql 只会在引用字段上创建索引:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.