Creating index fails

Question

I use mySQL database for my project and want to create index on column.

I'm using codefirst approach

Here is my migration

protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_RecruitmentAgencies_AbpTenants_TenantId",
            table: "RecruitmentAgencies");

        migrationBuilder.DropIndex(
            name: "IX_RecruitmentAgencies_TenantId",
            table: "RecruitmentAgencies");

        migrationBuilder.AddColumn<string>(
            name: "ContactEmail",
            table: "RecruitmentAgencies",
            nullable: true);
        
        migrationBuilder.CreateIndex(
            name: "IX_RecruitmentAgencies_ContactEmail",
            table: "RecruitmentAgencies",
            column: "ContactEmail");
    }

and when I try to apply it, I get this error

BLOB/TEXT column 'ContactEmail' used in key specification without a key length

How I can fix this?

Why do it in your program? Indexing is a one time thing. Do it in the DB directly — juergen d, Nov 06 '20 at 07:41
I use code first, so I need answer for this approach @juergend — Eugene Sukh, Nov 06 '20 at 07:44
You can only do partial indexes on `TEXT` type columns. This is a MySQL limitation. You'll need to figure out how to express that. — tadman, Nov 06 '20 at 07:45
Do you really need TEXT datatype (64 kbytes) for contact email? Maybe VARCHAR(255) is enough? — Akina, Nov 06 '20 at 07:56

score 0 · Answer 1 · answered Nov 06 '20 at 08:40

The issue is related that MySQL can index only the first N chars of a BLOB or TEXT column.

To solve this issue, try to remove the TEXT or BLOB column from the index or unique constraint or set another field as primary key. Besides, you could also try to use VARCHAR type and place a limit of length on it (or try to set the string MaxLength annotations).

Reference: MySQL error: key specification without a key length

Creating index fails

1 Answers1