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);