CODEWITHSUNDEEP
phpmysqllaravel-5
Hoffie
Hoffie

Reputation: 325

Error 1215; keep getting this foreign key error

I have seen tons of questions on stackOverflow 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.

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..

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

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

Upvotes: 1

Views: 86

Answers (1)

Shadow
Shadow

Reputation: 34232

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.

See mysql documentation on foreign keys:

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 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.

Upvotes: 1

Related Questions