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.
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 ----------------^^^
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.
Index key prefixes can be up to 767 bytes. See Section 12.1.8, “CREATE INDEX Syntax”.
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