CODEWITHSUNDEEP
phplaravel
livreson ltc
livreson ltc

Reputation: 733

Laravel 6. General error: 1215 Cannot add foreign key constraint

I'm running through the error message below after defining my database table engine to innoDB. I tried everything out there but it is not working. As you can tell I'm using bigInteger and unsignedBigInteger for my column type.

Error

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter tableusersadd constraintusers_account_status_id_foreignforeign key (account_status_id) referencesstatuses(id) on delete set null on update cascade)

 ************  USERS TABLE ***************************
 Schema::create('users', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('first_name');
        $table->string('last_name');
        $table->string('email')->unique();
        $table->string('phone')->nullable();
        $table->string('user_code')->unique();
        $table->integer('online_status')->boolean()->default(0);
        $table->unsignedBigInteger('account_status_id')->default(0);
        $table->string('image')->nullable();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
        $table->softDeletes();

        $table->foreign('account_status_id')->references('id')->on('statuses')->onUpdate('cascade')->onDelete('set null');
    });


*********STATUSES TABLE *******************
  Schema::create('statuses', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name')->unique();
        $table->string('slug')->unique();
        $table->timestamps();
        // $table->engine = 'InnoDB';
    });

******* CONFIG/database.php ****************
'mysql' => [
        'driver' => 'mysql',
        ...
        'engine' => 'innoDB',
        ...
        'options' => extension_loaded('pdo_mysql') ? array_filter([
            PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        ]) : [],
    ],

Upvotes: 1

Views: 1187

Answers (2)

Watercayman
Watercayman

Reputation: 8178

It looks like you haven't correctly defined the actual foreign key field on the Users table perhaps. Before the foreign designation, I would expect to see something like:

$table->unsignedBigInteger('account_status_id');  // No default 0
$table->foreign('account_status_id')->references('id')->on('statuses')->onUpdate('cascade')->onDelete('set null');

Note the loss of the ->default(0), as this will create problems against the nullable field.

The docs for migrations are here for V6.

Upvotes: 2

Foued MOUSSI
Foued MOUSSI

Reputation: 4813

I think your Schema Builder fails to create foreign key constraint because STATUSES TABLE is not already created.

Try to reverse migration orders (Change the dates that form the first part of the migration filenames so they're in the order you want (eg. for 2020_01_09_134109_create_users_table.php, the date & time is 2020-01-29, 13:41:09)

Run the migrations again.

Upvotes: 2

Related Questions