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?
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.