CODEWITHSUNDEEP
phpmysqllaravelunique-indexlongtext
Mátyás Horváth
Mátyás Horváth

Reputation: 99

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?

Upvotes: 1

Views: 2019

Answers (3)

clod986
clod986

Reputation: 2647

I don't know if you still need an answer, but here's how I made it work:

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

of course the 10 can be set to any length you need

Upvotes: 0

Mátyás Horváth
Mátyás Horváth

Reputation: 99

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.

Upvotes: 2

robbyrr
robbyrr

Reputation: 392

If you change: `

$table->longText('title')->comment('Event title');

to:

$table->string('title',200)->comment('Event title');

it will work, but i don't know if you expect titles with longer(200) text..

Upvotes: 0

Related Questions