Search code examples
mysqlmysql-error-1170

Problem in using TEXT field in mysql Table

This is very straight problem but I haven't figured out any solution yet. Scenario is I have 2 table in my DB. Source of 2nd table is dependent on 1st table's SOURCE_URL(can be more than 255 char so I have used TEXT).

create table SOURCES (
        SOURCES_PK int not null AUTO_INCREMENT primary key,
        SOURCE_URL text not null unique,
        DESCRIPTION varchar(255)
);

create table ASSERTIONGROUP (
        ASSERTION_PK int AUTO_INCREMENT primary key,
        LABEL varchar(255),
        SOURCE text not null,
        foreign key (SOURCE) references SOURCES(SOURCE_URL)
);

I am getting this error-

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

I have seen discussion in this post - MySQL error: key specification without a key length.
But can't figure out any solution.
I can remove unique from 1st table but then I can't assign foreign key constraint.

I know that TEXT field can't be unique so looking for an alternative.

Solution

  • In order to match against a long varchar or blob column you'll need to specify the index length:

    create table SOURCES (
  SOURCES_PK int not null AUTO_INCREMENT primary key,                    
  SOURCE_URL text not null unique,                    
  DESCRIPTION varchar(255),
  INDEX source_url (source_url(100)) );
//  Key length ----------------^^^

    For MyISAM

    The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

    For InnODB

    Index key prefixes can be up to 767 bytes. See Section 12.1.8, “CREATE INDEX Syntax”.

    See: http://dev.mysql.com/doc/refman/5.5/en/create-table.html