0

I'm trying to create foreign keys in Laravel 5.7 however when I migrate my table using artisan i am thrown the following error:

Illuminate\Database\QueryException:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table gateway_transactions add constraint gateway_transactions_user_id_foreign foreign key (user_id) references users (id) on delete CASCADE)

my migration :

    public function up()
    {
        Schema::create('gateway_transactions', function (Blueprint $table) {
            $table->engine = "innoDB";
            $table->unsignedBigInteger('id', true);
            $table->integer('user_id')->unsigned();
            $table->enum('provider', \Parsisolution\Gateway\GatewayManager::availableDrivers());
            $table->decimal('amount', 15, 2);
            $table->integer('order_id')->nullable();
            $table->string('currency', 3)->nullable();
            $table->string('ref_id', 100)->nullable();
            $table->string('tracking_code', 50)->nullable();
            $table->string('card_number', 50)->nullable();
            $table->enum('status', \Parsisolution\Gateway\Transaction::availableStates())
                ->default(\Parsisolution\Gateway\Transaction::STATE_INIT);
            $table->string('ip', 20)->nullable();
            $table->json('extra')->nullable();
            $table->timestamp('paid_at')->nullable();
            $table->nullableTimestamps();
            $table->softDeletes();
        });
        Schema::table('gateway_transactions', function(Blueprint $table) {
            $table->foreign('user_id')->references('id')->on('users')->onDelete('CASCADE');
        });
    }

users migration :

        Schema::create(config('access.table_names.users'), function (Blueprint $table) {
            $table->increments('id');
            $table->uuid('uuid');
            $table->string('first_name')->nullable();
            $table->string('last_name')->nullable();
            $table->string('email')->unique();
            $table->string('avatar_type')->default('gravatar');
            $table->string('avatar_location')->nullable();
            $table->string('password')->nullable();
            $table->timestamp('password_changed_at')->nullable();
            $table->tinyInteger('active')->default(1)->unsigned();
            $table->string('confirmation_code')->nullable();
            $table->boolean('confirmed')->default(config('access.users.confirm_email') ? false : true);
            $table->string('timezone')->nullable();
            $table->text('National_Code')->nullable();
            $table->char('phone_number', 11)->nullable()->unique();  
            $table->integer('phone_verify')->default(0);
            $table->char('mobile_number', 11)->nullable()->unique();  
            $table->integer('mobile_verify')->default(0);
            $table->text('state')->nullable();
            $table->text('city')->nullable();
            $table->text('address')->nullable();
            $table->text('path')->nullable();
            $table->char('postal_code', 10)->nullable();
            $table->timestamp('last_login_at')->nullable();
            $table->string('last_login_ip')->nullable();
            $table->rememberToken();
            $table->timestamps();
            $table->softDeletes();
        });
3
  • 1
    What's the data type for user_id? Perhaps it's an unisgned big int instead of an unsigned int, the data types must match exactly Commented May 22, 2019 at 15:38
  • @HCK I changed to unsignedBigInteger, but again it gets error Commented May 22, 2019 at 16:04
  • stackoverflow.com/a/52902308/5928015 Commented May 22, 2019 at 18:32

2 Answers 2

1

The type of the foreign key column must be exactly the same as the reference table.

If the ID of the Users table is a big integer:

$table->bigIncrements('id');

Your foreign key must be:

$table->unsignedBigInteger('user_id');

Then the migration will work correctly.

Sign up to request clarification or add additional context in comments.

1 Comment

I changed to unsignedBigInteger, but again it gets error
1

Change your code for this :

public function up()
{
    Schema::create('gateway_transactions', function (Blueprint $table) {
        $table->engine = "innoDB";
        $table->unsignedBigInteger('id', true);
        $table->integer('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users')->onDelete('CASCADE');
        $table->enum('provider', \Parsisolution\Gateway\GatewayManager::availableDrivers());
        $table->decimal('amount', 15, 2);
        $table->integer('order_id')->nullable();
        $table->string('currency', 3)->nullable();
        $table->string('ref_id', 100)->nullable();
        $table->string('tracking_code', 50)->nullable();
        $table->string('card_number', 50)->nullable();
        $table->enum('status', \Parsisolution\Gateway\Transaction::availableStates())
            ->default(\Parsisolution\Gateway\Transaction::STATE_INIT);
        $table->string('ip', 20)->nullable();
        $table->json('extra')->nullable();
        $table->timestamp('paid_at')->nullable();
        $table->nullableTimestamps();
        $table->softDeletes();
    });
}

I THINK - The following section is how I understand the way Laravel work.

Since you are creating your table into the first Schema, you have to put your foreign key into the creation.

The problem is that Laravel Migration will verify that everything will work before applying code, but the stage is the function up(). So everything in it is at the same stage. The migration think that your gateway_transactions doesnt exist, and it doesnt at the moment it verify everything is ok. It will when the code will exist.

3 Comments

Can you show us the creation of users table. We need to see what is the type of id colum in your users table.
I'm not sure what config('access.table_names.users') will create as name in your database. So, I changed my code of user_id to reflect your users database. It's now an integer. Be sure to have this part ->on('users') to be the same name that your users table is set. Tell me if the code work.
the config('access.table_names.users') is users. i'm changed code but again it error displayed. i create foreign keys for another table But they did not have error and just for this table This error exists

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.