Framework: Cannot add Unique Key to TEXT/BLOB column

[deleugpn]

• Laravel Version: 5.4.23
• PHP Version: 7.0.16
• Database Driver & Version: MySQL 5.7.15

Description:

When adding a text or binary column (which gets converted to BLOB), we are unable to make it a unique column. The MySQL Grammar will translate it into:

alter table `users` add unique `users_foo_unique`(`foo`)

And MySQL will fail because the size of the index have not been specified.

The appropriate Query would be something like:

alter table `users` add unique `users_foo_unique`(`foo(64)`)

Unfortunately, MySQL Grammer have no easy way to receive data about what size it should use. Also, columnize wouldn't be able to correctly compile the array of columns and their individual size.

Error Message (indexing)

[Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'sensitive_string' used in key specification without a key length

Steps To Reproduce:

Run the following Migration

        Schema::create('test_table', function (Blueprint $table) {
            $table->increments('id');
            $table->string('normal_string', 255);
            $table->text('sensitive_string')->unique();
        });

Possible Failing Test

    public function testAddingUniqueBinary()
    {
        $blueprint = new Blueprint('users');
        $blueprint->binary('foo')->unique();
        $statements = $blueprint->toSql($this->getConnection(), $this->getGrammar());
        $size = 64; // Ideally, the developer would provide this

        $this->assertEquals(2, count($statements));
        $this->assertEquals("alter table `users` add unique `users_foo_unique`(`foo`({$size}))", $statements[1]);
    }

[ThunderBirdsX3]

In my remember. You can't make text to unique. Can it be string.
$table->string('sensitive_string', 255)->unique();

[deleugpn]

@ThunderBirdsX3 the problem with string is that I cannot make it case-sensitive.

[sisve]

@themsaid Could you perhaps add a reason why was this closed? This is fully supported in mysql.

For string columns, indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length:

• Prefixes can be specified for CHAR, VARCHAR, BINARY, and VARBINARY column indexes.
• Prefixes must be specified for BLOB and TEXT column indexes.

... and ...

If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

Source: https://dev.mysql.com/doc/refman/5.7/en/create-index.html

[themsaid]

Feel free to open a PR, It does look like it needs special handling to create such syntax which is currently not supported, so a PR with code we can discuss would be great, if not I believe this should be submitted to the internals repo as a feature request.

[sisve]

@deleugpn Are you using the utf8mb4_bin collation? It should be _extremely_ case sensitive. I do not see any case-sensitive utf*-collations except the binary ones.

[shirshak55]

@sisve yes if it is supported by leading driver like mysql i guess laravel also support it :)

[wojcikcp]

So what is the solution for that? I still want to have text and unique column. Does Laravel support it?

[shirshak55]

@wojcikcp as of now laravel don't support it and i guess laravel 5.6 will also not support it as it is really minor issue. If possible use string and use unique column.

[wojcikcp]

Unfortunately string is too small For me. I will have then use raw query to create table I suppose

[npapratovic]

So..

$table->string is same as VARCHAR and can have lenght between 1 and 65536 characters
$table->text is same as TEXT and has fixed lenght of 65536 characters

Also, you can set $table->string as unique and with $table->text you can't set as unique.

If you are looking from perspective to optimize data size, it would be better to use string(VARCHAR) instead of text(TEXT)

[sisve]

$table->string is same as VARCHAR and can have lenght between 1 and 65536 characters
$table->text is same as TEXT and has fixed lenght of 65536 characters

If you are looking from perspective to optimize data size, it would be better to use string(VARCHAR) instead of text(TEXT)

These numbers are only somewhat true. They are true for anyone using a single-byte encoding. Assuming a utf8mb4 to support unicode and those numbers maxes out at 16383 characters.

From: https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-strings

The effective maximum number of bytes that can be stored in a VARCHAR or VARBINARY column is subject to the maximum row size of 65,535 bytes, which is shared among all columns. For a VARCHAR column that stores multibyte characters, the effective maximum number of characters is less. For example, utf8mb4 characters can require up to four bytes per character, so a VARCHAR column that uses the utf8mb4 character set can be declared to be a maximum of 16,383 characters.

That page also states that a VARCHAR(M) takes up "L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes". So, as long as our string values "may require" less than 256 bytes, we're only need up L+1 bytes. That means less than 64 utf8mb4 characters, meaning that a varchar(63) is the largest varchar we can declare that requires L+1 bytes, anything larger requires L+2 bytes, which is the same requirements as a TEXT-field.