Drop the index of the foreign using method dropForeign

[artshade]

Dear Community and Developers,

Thank you very much for the ineffably marvelous project! ✨

May I ask regarding the method dropForeign? Currently, when we create a new foreign, Laravel creates a corresponding index for the constraint. For example, let's check a quick case.

Case

Initial state of table "test'

dump(DB::select('SHOW CREATE TABLE `test`'));

array:1 [
  0 => {#7128
    +"Table": "test"
    +"Create Table": """
      CREATE TABLE `test` (\n
        `id` int unsigned NOT NULL AUTO_INCREMENT,\n
        `user_id` int NOT NULL,\n
        `notification` int unsigned DEFAULT NULL,\n
        `comment` text COLLATE utf8_unicode_ci,\n
        `created_at` timestamp NULL DEFAULT NULL,\n
        `updated_at` timestamp NULL DEFAULT NULL,\n
        `deleted_at` timestamp NULL DEFAULT NULL,\n
        PRIMARY KEY (`id`),\n
        KEY `user_id_IDX` (`user_id`),\n
        KEY `test_created_at_index` (`created_at`),\n
      ) ENGINE=InnoDB AUTO_INCREMENT=524 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci
      """
  }
]

Create a foreign for table "notifications"

Schema::table('test', function (\Illuminate\Database\Schema\Blueprint \$table) {
  \$table->foreign('notification')
    ->references('id')->on('notifications')
    ->restrictOnUpdate()
    ->restrictOnDelete();
});

dump(DB::select('SHOW CREATE TABLE `test`'));

array:1 [
  0 => {#7128
    +"Table": "test"
    +"Create Table": """
      CREATE TABLE `test` (\n
        `id` int unsigned NOT NULL AUTO_INCREMENT,\n
        `user_id` int NOT NULL,\n
        `notification` int unsigned DEFAULT NULL,\n
        `comment` text COLLATE utf8_unicode_ci,\n
        `created_at` timestamp NULL DEFAULT NULL,\n
        `updated_at` timestamp NULL DEFAULT NULL,\n
        `deleted_at` timestamp NULL DEFAULT NULL,\n
        PRIMARY KEY (`id`),\n
        KEY `user_id_IDX` (`user_id`),\n
        KEY `test_created_at_index` (`created_at`),\n
        KEY `test_notification_foreign` (`notification`),\n
        CONSTRAINT `test_notification_foreign` FOREIGN KEY (`notification`) REFERENCES `notifications` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,\n
      ) ENGINE=InnoDB AUTO_INCREMENT=524 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci
      """
  }
]

As we see, alongside the foreign constraint test_notification_foreign there an additional index test_notification_foreign has just been automatically created. The rationale behind the creation is fairly clear, since the performance difference with an without such an index may be crucial.

Drop the created foreign

Schema::table('test', function (\Illuminate\Database\Schema\Blueprint \$table) {
  \$table->dropForeign(['notification']);
});

dump(DB::select('SHOW CREATE TABLE `test`'));

array:1 [
  0 => {#7128
    +"Table": "test"
    +"Create Table": """
      CREATE TABLE `test` (\n
        `id` int unsigned NOT NULL AUTO_INCREMENT,\n
        `user_id` int NOT NULL,\n
        `notification` int unsigned DEFAULT NULL,\n
        `comment` text COLLATE utf8_unicode_ci,\n
        `created_at` timestamp NULL DEFAULT NULL,\n
        `updated_at` timestamp NULL DEFAULT NULL,\n
        `deleted_at` timestamp NULL DEFAULT NULL,\n
        PRIMARY KEY (`id`),\n
        KEY `user_id_IDX` (`user_id`),\n
        KEY `test_created_at_index` (`created_at`),\n
        KEY `test_notification_foreign` (`notification`),\n
      ) ENGINE=InnoDB AUTO_INCREMENT=524 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci
      """
  }
]

As we see, the foreign constraint test_notification_foreign got removed but the index test_notification_foreign. This may require an additional explicit command to be executed after the dropForeign:

\$table->dropForeign(['notification']);
\$table->dropIndex('test_notification_foreign');

Question

1. Is it possible to have an additional parameter in the method dropForeign (e.g. index of type string|bool where string would define an explicit name, and bool - an self-generated by the framework)?
2. Is the rationale of leaving the automatically created index the performance or the unknown name of the key previously created, considering the following condition?:

framework/src/Illuminate/Database/Schema/Blueprint.php

Lines 1633 to 1640 in 1d7e000

	// If no name was specified for this index, we will create one using a basic
	// convention of the table name, followed by the columns, followed by an
	// index type, such as primary or index, which makes the index unique.
	$index = $index ?: $this->createIndexName($type, $columns);
	return $this->addCommand(
	$type, compact('index', 'columns', 'algorithm')
	);

Best and kind regards

Related

- Dropping column with foreign key Laravel error

[hafezdivandari]

Currently, when we create a new foreign, Laravel creates a corresponding index for the constraint. For example, let's check a quick case.

This is not true, Laravel doesn't create an index for the foreign constraint, MySQL does! MySQL requires indexes on foreign keys and referenced keys, and an index is created on the referencing table automatically if it does not exist.

Is it possible to have an additional parameter in the method dropForeign (e.g. index of type string|bool where string would define an explicit name, and bool - an self-generated by the framework)?

Not a good idea, because it's MySQL specific, for example PostgreSQL doesn't create an index / doesn't need an index on foreign constraint.

Is the rationale of leaving the automatically created index the performance or the unknown name of the key previously created, considering the following condition?:

Didn't understand your question!

What we can improve here?

We can improve dropForeign and dropIndex methods. Currently when passing column name(s) as array to these function, Laravel will use the conventional name to drop foreign / index. But it's much better to use this column array to find foreign / indexes names (using Schema::getIndexes() and Schema::getForeigns() methods) and remove them. then you can simply do:

$table->dropForeign(['notification']); // drop any foreign key constraint of `['notification']` column not just `test_notification_foreign`
$table->dropIndex(['notification']); // drops any indexes of `['notification']` column not just `test_notification_index`