Problem in using TEXT field in mysql Table

Question

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.

What version of mysql? According to the documentation "Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions." http://dev.mysql.com/doc/refman/5.0/en/char.html Also, why not reference the primary key of the first table? — AllisonC, Jun 16 '11 at 15:28

score 3 · Accepted Answer · answered Jun 16 '11 at 15:54

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

Thanks for your time Johan. But now I am referencing primary key. — , Jun 17 '11 at 10:41

VGE · Answer 2 · 2011-06-16T16:42:35.923

2

Unfortunatly you can not index a whole BLOB and TEXT because index key are limited in length.

You can create FullText index on myasam engine only.

When I need to implement a uniqueness constraint I usually use a specific columns which contains a Hash (SHA or MD5) of the Text, and some code to handle Hash clash.

It is a little bit ugly but it works

edited Jun 16 '11 at 16:42

answered Jun 16 '11 at 15:25

VGE

4,171
18
17

2

Blob/text columns CAN be indexed, but only a limited subset of the whole field, 767 or 1000 bytes, depending on engine: http://dev.mysql.com/doc/refman/5.0/en/column-indexes.html – Marc B Jun 16 '11 at 15:34

Problem in using TEXT field in mysql Table

2 Answers2

Linked