I currently have a database in my project which has already been put in production. But I didn’t use constraints in the previous migrations. Now I have tables products
, shops
and an intermediate table product_shop
. The problem is that if I delete any product, that has been put on some shop, the pivot is still kept in the intermediate table. I need to force the referential integrity of my database, even though no product/shop has been changed/deleted yet.
I don’t want to use Laravel’s event listeners, because they do not work when I delete an object without retrieving it first. Let’s consider this existing structure, where I have data I don’t want to lose:
shops - id (int, auto-increment, index) - domain (string) products - id (int, auto-increment, index) - name (string) - price (float) product_shop - id (int, auto-increment, index) - product_id (int, foreign_key) - shop_id (int, foreign_key)
Now I want to create a migration, where I set constraints to product_shop.product_id
and product_shop.shop_id
with onDelete: CASCADE, onUpdate: CASCADE
. So no matter where or how I will delete a product – if I delete one, all the related pivots will be deleted as well.
But how am I suppose to change constraints in migration->up()
& migration->down()
?
class EstablishConstraints extends Migration { public function up() { Schema::table('product_shop', function (Blueprint $table) { $table->someMagic('product_id')->moreMagic('CASCADE'); // What here? $table->someMagic('shop_id')->moreMagic('CASCADE'); // ...and here? }); } public function down() { Schema::table('product_shop', function (Blueprint $table) { $table->reverseMagic('product_id'); // How to reverse it? $table->reverseMagic('shop_id'); // ...on both columns? }); } }
Thank you 🙂
Advertisement
Answer
Solution found:
class EstablishConstraints extends Migration { public function up() { Schema::table('product_shop', function (Blueprint $table) { $table->foreignId('product_id')->change() ->constrained() ->cascadeOnDelete() ->cascadeOnUpdate(); $table->foreignId('shop_id')->change() ->constrained() ->cascadeOnDelete() ->cascadeOnUpdate(); }); } public function down() { Schema::table('product_shop', function (Blueprint $table) { $table->dropForeign('product_shop_product_id_foreign'); $table->dropForeign('product_shop_shop_id_foreign'); }); } }