orm: Unable to create unique keys on TEXT columns

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?

@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.

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))
);

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