Foreign key constraint is incorrectly formed laravel 6

0
votes

I have 2 tables and when I try to migrate return to me this error:

General error: 1005 Can't create table usee_anbari.#sql-473_21177 (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table companies add constraint companies_access_id_foreign foreign key (access_id) references accesses (id) on delete cascade)

this is my tables:

public function up()
{
    Schema::create('companies', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name');
        $table->text('address');
        $table->string('tel1');
        $table->string('tel2');
        $table->integer('owner');
        $table->unsignedBigInteger('access_id');
        $table->string('depot_number')->default(2);
        $table->timestamps();


        $table->foreign('access_id')->references('id')->on('accesses')
            ->onDelete('cascade');
    });
}

and another one :

public function up()
{
    Schema::create('accesses', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('type');
        $table->string('description');
        $table->timestamps();
    });
}

what I miss?

2
mysqldatabaselaravelmariadblaravel-6
Is that the order in which your tables are created? Table accesses needs to exist before you can reference it - brombeer
@kerbholz in my localhost it's done successfully but in the server just return me this error and I get the error when I want to create companies table - alireza alizade
well one is bigIncrements and the other one is unsignedBigInteger, instead you have to use bigInteger - Alberto Sinigaglia
@AlbertoSinigaglia bigIncrements is unsignedBigInteger - lagbox

2 Answers

0
votes

In your database/migrations folder, sort by name. Then make sure create_accesses_table is before create_companies_table:

enter image description here

0
votes

I have faced the same problem and I'm here describing how to I have solved it.

You have to use unsignedBigInteger instead of unsignedInteger. See the below code:

Schema::create('orders', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedBigInteger('user_id')->nullable(); // Changed here
    $table->string('ip_address')->nullable();
    $table->string('email')->nullable();
    $table->text('message')->nullable();
    $table->timestamps();

    $table->foreign('user_id')
    ->references('id')->on('users')
    ->onDelete('cascade');
});

Where you will use foreign there use the unsignedBigInteger (if have id). If still not clear, please put a comment.