Orm: Unable to create unique keys on TEXT columns

[toby-griffiths]

If you add unique=true to an entity's column annotation for a type="text" column, it reports the error…

SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'name' used in key specification without a key length

… so you try to add it using the @Table annotation…

    /**
    * @ORM\Table(uniqueConstraints={@ORM\UniqueContraint(columns={"name(30)"})})
    */

… but now you get the error…

[Doctrine\DBAL\Schema\SchemaException]
  There is no column with name 'name(30)' on table 'service_provider'.

Is there currently a way of implementing this without submitted a PR? I've dug into the code, but can't see a way around it.

If not, what's the recommended way to fix this? Happy to submit a PR with a little guidance.

[davoc]

I just tried using PHPMyAdmin to add a unique constraint to a text field and it throws this error...the only thing you can do is to use VARCHAR instead of TEXT or LONGTEXT for a unique index https://techjourney.net/mysql-error-1170-42000-blobtext-column-used-in-key-specification-without-a-key-length/

[lcobucci]

@toby-griffiths as @davoc said, you can only create a unique index for a column with known length (for string columns, sure).

[lcobucci]

I'll close the issue since the answer has been provided.

[toby-griffiths]

This isn't correct. You can have indexes on TEXT fields… you just need to specify the length of the index. Perhaps this could be re-opened so that someone who has time could add support for this?

[lcobucci]

This isn't correct. You can have indexes on TEXT fields… you just need to specify the length of the index. Perhaps this could be re-opened so that someone who has time could add support for this?

@toby-griffiths sure but have you tried to configure the length of the column on the property (instead of the size of the index)?

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/basic-mapping.html#property-mapping

[toby-griffiths]

I'm pretty sure I dug into the code that generates the code for the index part of the query and there's nothing there to include a length, so specifying the 'length' attribute on the field will not affect the unique index.
This could be a way to implement this… but this would probably be confusing. Maybe using 'index_length' instead attribute should be used instead.

I'm sorry, I'm not in the project right now, so can't confirm these facts.

[patrickallaert]

This issue is not addressed yet. Field max length has nothing to do with index length, by default, they are created with the same length by databases but there are many reasons (including performance) why you want a partial index.
While a length is mandatory on a BLOB/TEXT column index, it can be extremely useful for (VAR)CHAR too.

Example use case:
Column containing a sha512 checksum where all first 8 chars are known to be unique

CREATE TABLE `files` (
  chksum char(128) NOT NULL,
  KEY chksum_idx (chksum(8))
);

[GPHemsley]

I believe this is still an issue. Can you at least reopen the ticket?

[Ocramius]

You can create indexes via explicit DB migrations: doesn't need to happen through ORM schema tools.

[igneus]

Yes, we can. And the latest https://github.com/doctrine/DoctrineMigrationsBundle attempts to destroy such indexes (generates SQL removing the manually added lengths) whenever you run console doctrine:migrations:diff.

[IMM0rtalis]

+1 from my side as well.

[Ocramius]

generates SQL removing the manually added lengths

Generated migrations are by no means to be committed as they are: migrations on larger databases are absolutely not to be performed directly as generated by ORM schema tool diffs, because you'll basically cripple your production environment anyway (by locking production schema symbols for hours or even days).

Please don't try to fix every issue with the schema diffing tool if a developer is explicitly supposed to look at its output and operate on it anyway, because you are pushing a lot of complexity upstream that will just lead to more bugs down the line, for no strong reason.

[GPHemsley]

@Ocramius If the database is not in sync with the schema, the schema diff tool will always generate a query to "correct" that difference. If the diff tool does not support a certain state of the database, it becomes effectively useless—defeating the purpose of having it in the first place.

[Ocramius]

It is a tool trying to reconcile reality with developer wishes, and it is up to the developer to take the output from this tool and match it to their vision

[igneus]

It may be worth noting that until recently (I can later find exact releases were it useful) the schema diff tool ignored the index length and almost everyone (at least in my team) was happy.
Manually adding index length to a migration creating the index is no issue for me, but looking for lines to delete from every subsequent migration once the index is in place is annoying and error-prone, especially when collaborating in a larger team.

[jnvsor]

Manually adding index length to a migration creating the index is no issue for me, but looking for lines to delete from every subsequent migration once the index is in place is annoying and error-prone

This is the gist of the problem. Right now you have to choose between using indices on lengthless types like text or basically writing all your migrations by hand.

I'm not going to ask for ORM support for lengths on indices, I'm not even going to ask for it to stop producing syntactically invalid queries. I just want it to ignore index length when comparing.

Right now the first code it generates has a syntax error (Because you can't put indices on text fields in mysql/mariadb without specifying the size) but you can fix that by hand.

Once you've fixed that and the database is exactly what it should be, diff shouldn't have any output. Instead it removes the (good) index and puts the syntax error back in. That is a bug. And one that other people here have noted makes the tool basically useless unless you're willing to throw away your indices.

That said, perhaps the title of this issue isn't precise enough now?