phpmysqllaravelunique-indexlongtext

laravel migration - unique combination of columns type longText


I've got a table with colums like this:

...
$table->longText('title')->comment('Event title');
$table->decimal('start_year',13,0)->nullable(true)->comment('Year part of beginning of event date');
$table->decimal('start_month',2,0)->default(0)->comment('Month part of beginning of event date');
$table->decimal('start_day',2,0)->default(0)->comment('Day part of beginning of event date');
...

I need a combined unique index based on these columns. But 'title' is a longText.

This one is not working:

$table->unique([['title','255'], 'start_year', 'start_month', 'start_day'],'unique_title_and_date');

Migration tool sais:

[ErrorException]
  strtolower() expects parameter 1 to be string, array given

This one is not working also:

$table->unique(['title(255)', 'start_year', 'start_month', 'start_day'],'unique_title_and_names');

Migration tool sais:

  [PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'title(255)' doesn't exist in table

This one is not working also:

$table->unique(['title', 'start_year', 'start_month', 'start_day'],'unique_title_and_names');

Migration tool sais:

[Illuminate\Database\QueryException]
  SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'title' used in key specification without a key length 

How to make migration tool eat this command?


Solution

  • Finally I've found a kind of solution. Because I need a unique index on a text like column combined with other columns, it seems a possible solution to use a DB::unprepared method in migration.

    So I've create a class named AddUniquesToEvents like this:

    <?php
    
    use Illuminate\Support\Facades\Schema;
    use Illuminate\Database\Schema\Blueprint;
    use Illuminate\Database\Migrations\Migration;
    
    class AddUniquesToEvents extends Migration
    {
        /**
         * Run the migrations.
         *
         * @return void
         */
        public function up()
        {
             DB::unprepared('ALTER TABLE timeline_events
                                    ADD UNIQUE key u_title_and_dates (title(64),start_year, start_month,start_day)'
                              );
        }
    
        /**
         * Reverse the migrations.
         *
         * @return void
         */
        public function down()
        {
           DB::unprepared('ALTER TABLE timeline_events drop index `u_title_and_dates`');
        }
    }
    

    Migration makes it run successfully.