CODEWITHSUNDEEP
laravel-5database-migration
Bas
Bas

Reputation: 2400

Laravel migration add foreign key gives error foreign key (`user_id`)

When i php artisan migrate then i am getting an error see below. Order migrations is users, companies and pivotmigration.

When i delete a user all companies has to delete and when i delete a company all users has to be deleted.

What do i do wrong?

User.php

    Schema::create('users', function (Blueprint $table) {
        $table->engine = "InnoDB";
        $table->bigIncrements('id');
        $table->string('name');
        $table->string('email')->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });

Companies.php

    Schema::create('companies', function (Blueprint $table) {
        $table->engine = "InnoDB";
        $table->increments('id')->unsigned();
        $table->string('companyname');
        $table->string('address');
        $table->integer('housenumber');
        $table->string('postalcode');
        $table->string('city');
        $table->string('province');
        $table->string('email');
        $table->string('phonenumber');
        $table->timestamps();
    });

CreateUserCompanyPivotTable.php

    Schema::create('user_company', function (Blueprint $table) {
        $table->engine = "InnoDB";
        $table->integer('user_id')->unsigned();
        $table->integer('company_id')->unsigned();
    });

    Schema::table('user_company', function (Blueprint $table) {
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        $table->foreign('company_id')->references('id')->on('companies')->onDelete('cascade');
    });

Error: 

Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 

Cannot add foreign key constraint (SQL: alter table `user_company` add constraint `user_company_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade)

Upvotes: 1

Views: 1334

Answers (1)

Zendy
Zendy

Reputation: 774

The migration fail because the data type for user_id in user_company does not match with id in users.

You used bigIncrement() in users table which create a auto_increment field with UNSIGNED BIGINT as type.

In user_company table, you create user_id with integer()->unsigned() which create an auto_increment field with UNSIGNED INT as type.

MySQL needs two field to be same type when creating foreign key.

To solve this issue, you should create user_id with bigInteger()->unsigned() instead.

Schema::create('user_company', function (Blueprint $table) {
    $table->engine = "InnoDB";
    $table->bigInteger('user_id')->unsigned();
    $table->bigInteger('company_id')->unsigned();
    // You don't need separate schema code to create foreign
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    $table->foreign('company_id')->references('id')->on('companies')->onDelete('cascade');
});

Upvotes: 1

Related Questions