In Laravel 5, migration to drop composed unique key doesn't work

Question

Trying to make a migration to drop composed unique key fails without errors.

I've created a migration with php artisan make:migration and edited the code. So I have this

public function up()
{
    Schema::table('ques_trilha_itens', function (Blueprint $table) {
        $table->dropUnique('trilha_itens_trilha_id_questao_id_unique');
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('ques_trilha_itens', function (Blueprint $table) {
        $table->unique(['trilha_id', 'questao_id']);
    });
}

The string 'trilha_itens_trilha_id_questao_id_unique' is the one that is displayed as the composed unique key in MySQL. So I think that string is to be used do drop the two composed keys.

But when running php artisan migrate, nothing happens, no error messages, and the migration is not executed.

I tried substitute the string in dropUnique to give the table's name as the first term ('ques_trilha_itens_trilha_id_questao_id_unique') and nothing.

Is something I'm missing?

UPDATE:

MySQL command SHOW CREATE TABLE ques_trilha_itens give:

CREATE TABLE `ques_trilha_itens` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `trilha_id` int(10) unsigned NOT NULL,
  `questao_id` int(10) unsigned NOT NULL,
  `primeiro_item` tinyint(1) NOT NULL,
  `item_principal_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `chave` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `trilha_itens_trilha_id_questao_id_unique` (`trilha_id`,`questao_id`),
  UNIQUE KEY `ques_trilha_itens_chave_unique` (`chave`),
  KEY `trilha_itens_questao_id_foreign` (`questao_id`),
  KEY `ques_trilha_itens_item_principal_id_foreign` (`item_principal_id`),
  CONSTRAINT `ques_trilha_itens_item_principal_id_foreign` FOREIGN KEY (`item_principal_id`) REFERENCES `ques_trilha_itens` (`id`) ON DELETE SET NULL,
  CONSTRAINT `trilha_itens_trilha_id_foreign` FOREIGN KEY (`trilha_id`) REFERENCES `ques_trilhas` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

have you tried >> dropForeign(). The problem is that ->dropUnique is on the column name. dropForeign() is on the relationship — A H Bensiali, Mar 29 '16 at 18:09
Yes, without success. But what I want is to undo the unique key not the foreigns keys. dropUnique is in the relationship too. — Caco, Mar 29 '16 at 18:13
use `SHOW CREATE TABLE ` to get the actual constraint name which should be passed to dropUnique(). — Vikas, Mar 29 '16 at 19:44
The constraint name your are using is correct according to the result of `SHOW CREATE TABLE`, so it should have worked. Run `composer dump-autoload` or try adding a column in the above migration just to verify if this migration is running or not. — Vikas, Mar 29 '16 at 20:08

score 1 · Answer 1 · answered Mar 30 '16 at 11:02

Well, by trial and error I figured out what I was missing.

The problem is that MySQL doesn't like you to try drop a foreign referenced composed key before you drop these foreign keys themselves before.

CONSTRAINT `ques_trilha_itens_item_principal_id_foreign` FOREIGN KEY (`item_principal_id`) REFERENCES `ques_trilha_itens` (`id`) ON DELETE SET NULL,
CONSTRAINT `trilha_itens_trilha_id_foreign` FOREIGN KEY (`trilha_id`) REFERENCES `ques_trilhas` (`id`)

Like that, we have to drop the index (KEY) before too.

KEY `trilha_itens_questao_id_foreign` (`questao_id`)

Only after that, I could drop the composed keys ['questao_id', 'trilha_id'].

So at the end my migration was so

/**
 * Run the migrations.
 *
 * @return void
 */

public function up()
    {
        Schema::table('ques_trilha_itens', function (Blueprint $table) {
            // Remove chaves estrangeiras, índices e chaves não estrangeiras
            $table->dropForeign('trilha_itens_questao_id_foreign');
            $table->dropForeign('trilha_itens_trilha_id_foreign');
            $table->dropIndex('trilha_itens_questao_id_foreign');
            $table->dropUnique('trilha_itens_trilha_id_questao_id_unique');

            // Refaz relações, agora sem a chave dupla
            $table->foreign('questao_id')->references('id')->on('ques_questoes');
            $table->foreign('trilha_id')->references('id')->on('ques_trilhas');
        });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('ques_trilha_itens', function (Blueprint $table) {
        $table->unique(['questao_id', 'trilha_id']);
    });
}

score 0 · Answer 2 · answered Mar 30 '16 at 00:52

I was trying to figure out what was going then it came to me.

this is your problem

$table->dropUnique('trilha_itens_trilha_id_questao_id_unique'); <<< here be the problem

Your problem is that youre trying to do too many things at the same time.

to drop a unique key. You have to use the appropriate nomenclature.

table_name_column_name_unique 1) table_name _ 2) column_name _ 3) unique

so you would have to run your "dropUnique()" twice.

$table->dropUnique('trilha_itens_trilha_id_unique'); //for trilha_id
$table->dropUnique('trilha_itens_questao_id_unique'); //for questao_id

Try that, let me know how it goes. Of course make sure you run composer dump-autoload Happy coding :)

Hakim

Thanks Hakim. I just figured out what was going on. The problem was trying to remove the composed key without remove foreign keys and index (KEY) before that. I will post the answer. — Caco, Mar 30 '16 at 10:50

In Laravel 5, migration to drop composed unique key doesn't work

2 Answers2