How do I create a unique composite index in Laravel 6?

Question

I'm new to Laravel and am using Laravel 6. I'm trying to create a unique composite index on two columns of my MySQL table but when I run the migration, I get this error:

Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table sleep_diary_entries add index primaryKey(client, sleep_date))

The docs say I've written the statement correctly. Here are the relevant lines of the table definition:

       Schema::create('sleep_diary_entries', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('client');
        $table->date('sleep_date');
        $table->index(['client', 'sleep_date'], 'primaryKey'); //combination of client and sleep_date must be unique

MySQL defines a string as VARCHAR(255) and a date should be about 10 characters, give or take. I don't understand how this composite index could be over 767 characters long.

This index is vital to make my app work correctly: the combination of the user id and the date in an index ensures that the user can only create one record for each day.

How do I coax Laravel to accept my index?

Also, the docs are not very clear at all on whether a composite index can be unique or whether

table->unique(['client', 'sleep_date']);

is valid or if unique() can only be applied to a single column. I need my composite index to be unique.

EDIT: Once I added the line Sehdev suggested to AppServiceProvider.php, I was able to create my index successfully as shown in my question. However, via trial and error, I quickly verified that the index was NOT unique and would have allowed a given user to add multiple records for each date against the needs of my app. I changed the definition to:

table->unique(['client', 'sleep_date'], 'UniqueKey');

This time, the index was both composite AND unique, exactly as I wanted.

I'm still baffled on how those two columns were deemed to have a combined length greater than 757 bytes but I don't have time to obsess over it; I want to work on my app.

In short: Laravel >=5.4 defaults to using `utf8mb4` character set, which Mysql <5.7.7 and MariaDb <10.2.2 can't handle. [Damn emojis](https://stackoverflow.com/a/30074553/4468423). — Erich, Feb 21 '20 at 03:26

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

This issue is already listing in Laravel git repository here

MySQL defines a string as VARCHAR(255)

To Resolve this:

1.Goto app/Providers/AppServiceProvider.php

Add below lines:

use Illuminate\Support\Facades\Schema; // use this class

public function boot()
{
    Schema::defaultStringLength(191); // add this line in boot method
}

OR

Manually Change VARCHAR(255) to VARCHAR(191) and then try.

How do I create a unique composite index in Laravel 6?

1 Answers1