khsing / laravel-world

provide countries, states, and cities relations and database.
MIT License
427 stars 89 forks source link

767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5.6 #17

Closed RedPlatypus closed 5 years ago

RedPlatypus commented 5 years ago

Some of the multi column indices in 2017_04_08_163453_create_world_countries_table.php 2017_04_08_163453_create_world_divisions_table.php Exceeded the 767 byte limit of MySQL version 5.6

I modified the tables so instead of names being 255 chars, they were 170 chars. Not sure if this will have side effects later on, but thought I would post here just incase it helps someone else.

Schema::create('world_countries', function(Blueprint $table)
{
    $table->increments('id')->comment('Auto increase ID');
    $table->integer('continent_id')->unsigned()->comment('Continent ID');
    $table->string('name', 170)->default('')->comment('Country Common Name');
    $table->string('full_name', 255)->nullable()->comment('Country Fullname');
    $table->string('capital', 255)->nullable()->comment('Capital Common Name');
    $table->string('code', 4)->nullable()->comment('ISO3166-1-Alpha-2');
    $table->string('code_alpha3', 6)->nullable()->comment('ISO3166-1-Alpha-3');
    $table->string('emoji', 16)->nullable()->comment('Country Emoji');
    $table->boolean('has_division')->default(0)->comment('Has Division');
    $table->string('currency_code', 3)->nullable()->comment('iso_4217_code');
    $table->string('currency_name', 128)->nullable()->comment('iso_4217_name');
    $table->string('tld', 8)->nullable()->comment('Top level domain');
    $table->string('callingcode', 8)->nullable()->comment('Calling prefix');
    $table->unique(['continent_id','name'], 'uniq_country');
});
Schema::create('world_divisions', function(Blueprint $table)
{
    $table->increments('id')->comment('Auto Increase ID');
    $table->integer('country_id')->unsigned()->comment('Country ID');
    $table->string('name', 175)->default('')->comment('Division Common Name');
    $table->string('full_name', 255)->nullable()->comment('Division Full Name');
    $table->string('code', 64)->nullable()->comment('ISO 3166-2 Code');
    $table->boolean('has_city')->default(0)->comment('Has city?');
    $table->unique(['country_id','name'], 'uniq_division');
});