[FIXED] How to add constraints to existing keys using laravel migration

Issue

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

Solution

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

  }
}

Answered By - David Sojka

Answer Checked By - David Marino (PHPFixing Volunteer)