Skip to content
Advertisement

Laravel 7 migration error. Cannot add foreign key constraint

Try to create foreign key in Laravel 7 but when I migrate tables using artisan it gives error

SQLSTATE[HY000]: General error: 3780 Referencing column ‘remote_id’ and referenced column ‘parent_id’ in foreign key constraint ‘products_remote_id_foreign’ are incompatible. (SQL: alter table products add constraint products_remote_id_foreign foreign key (remote_id) references categories (parent_id) on delete cascade)

My categories table

Schema::create('categories', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('parent_id');
            $table->tinyInteger('depth');
            $table->string('name');
            $table->string('slug');
            $table->text('description');
            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->default(DB::raw('NULL ON UPDATE CURRENT_TIMESTAMP'))->nullable();
        });

My products table

Schema::create('products', function (Blueprint $table) {
            $table->id();

            $table->unsignedBigInteger('remote_id');
            $table->foreign('remote_id')->references('parent_id')->on('categories')->onDelete('cascade');
            
            $table->unsignedBigInteger('category_id');
            $table->foreign('category_id')->references('id')->on('categories')->onDelete('cascade');

            $table->string('name');
            $table->text('description');
            $table->integer('price');
            $table->tinyInteger('status')->default(1);
            $table->integer('qty');
            $table->string('barcode')->nullable();
            $table->string('image');
            $table->text('images')->nullable();
            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->default(DB::raw('NULL ON UPDATE CURRENT_TIMESTAMP'))->nullable();
        });

Any ideas as to what I’ve done wrong? Thanks for help!!!

Advertisement

Answer

As mentioned by aynber, for foreign keys to be compatible, they must be of the same type. Your remote_id in your products table is an unsignedBigInteger while the key you are trying to reference in your categories table, parent_id, is an integer. To fix this either change category_id in your products table to an integer or change parent_id in your categories table to an unsignedBigInteger.

Edit:

I looked a little more into foreign keys and found this useful answer on another post. Foreign keys need to have a unique constraint or be a primary key. Since your remote_id column is referencing parent_id which is neither a primary key nor has a unique constraint, you are getting this error.

The solution given in the other question for adding a unique constraint is running this command(modified for your tables):

alter table categories add constraint uq1 unique (parent_id);

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement