错误1215;继续得到这个外键错误

I have seen tons of questions on * regarding my error, but for some reason the answers does not apply to the following:

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

I have three tables as followed:

我有三张桌子如下:

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();
    });
}

And i try to connect them NOT by increments('id') but by integer('activity_id) . below is the migration for connecting the foreign keys.

我尝试连接它们不是通过增量('id')而是通过整数('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');
    });
}

For some reason I keep getting a Foreign key constraint.. Is it because laravel does not like me trying to reference to a different ID? if so, is there a workaround for it..

出于某种原因,我一直得到一个外键约束..是不是因为laravel不喜欢我试图引用不同的ID?如果是的话,是否有解决方法..

Or is it simply a misspell that I am overlooking?

或者它只是我忽略的拼写错误?

EDIT: changed from unsignedInteger to just integer for all the columns that should be foreign. EDIT: added ->nullable() to activity_id so it would be exactly the same with other columns

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

EDIT: Thanks to @shadow I figured out that there is no index on activity_id.. all i had to do was make it unique.

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

1 个解决方案

#1

The problem is that activity_id is defined as integer, whike the fields holding the foreign keys are defined as unsigned integers. This is a type mismatch. All fields should be defined as integer or unsigned integer.

问题是activity_id被定义为整数,就像持有外键的字段被定义为无符号整数一样。这是一种类型不匹配。所有字段都应定义为整数或无符号整数。

See mysql documentation on 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.

外键和引用键中的相应列必须具有相似的数据类型。整数类型的大小和符号必须相同。字符串类型的长度不必相同。对于非二进制(字符)字符串列,字符集和排序规则必须相同。

Furthermore, you need to create indexes on both the referencing and referenced fields for a foreign key to be set up. Mysql only creates an index on the referencing field if one does not exists:

此外,您需要在引用和引用字段上创建索引,以便设置外键。 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.

MySQL需要外键和引用键上的索引,以便外键检查可以很快并且不需要表扫描。在引用表中,必须有一个索引,其中外键列以相同的顺序列为第一列。如果索引不存在,则会自动在引用表上创建此索引。

#1