1958

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.

Answer1:

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 ----------------^^^

<strong>For MyISAM</strong>

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.

<strong>For InnODB</strong>

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

Answer2:

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

Recommend

  • Java Error in eclipse: Could not find or load main class length
  • for-loop statement understanding
  • Returning correct .length even though length is 1
  • Rectangular Nesting - Convergence to optimal solution using Simulated Annealing
  • Ensure Ruby version in Nix Dev Environment when using latest version
  • and events (INotifyPropertyChanged, specifically)
  • What is the difference between Google Developers Console project and a Google **Play** Developers Co
  • How to get a list of all blobs in a repository in Git
  • Best style for iterating over a small number of items in Python?
  • Change behaviour of Print button in ReportViewer C#
  • How to get or calculate size of Azure File/Share or Service
  • How to set a private variable for a jQuery plugin?
  • Editable Combo Box Javascript and HTML
  • How to Compose OSGi Based project with C++ based project?
  • How do I include screenshots of the full page in my serenity report (and not only of the viewport) u
  • Swift string variables localization
  • Get UILabel out of UIButton
  • Null ImageView Reference
  • Selecting a subset of data in ServiceStack.OrmLite
  • How do I change the kernel/python version for iPython?
  • Set step size polar plot matplotlib python
  • Using Nested Table variables / Collections in SQL inside PL/SQL blocks
  • EditText is covered by Keyboard
  • event.getSource() returns null Accessibility in android
  • How to resolve permission denied maybe missing internet permission?
  • ApplePay PKPaymentAuthorizationViewController always shows processing
  • xcode don't localize specific strings
  • Ember.js model to be organised as a tree structure
  • Groovy: Unexpected token “:”
  • Swift: Switch statement fallthrough behavior
  • Replace value with Factor in r data.table
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • java.lang.NoClassDefFoundError: com.parse.Parse$Configuration$Builder on below Lollipop versions
  • Which linear programming package should I use for high numbers of constraints and “warm starts” [clo
  • Is there a mandatory requirement to switch app.yaml?
  • Hits per day in Google Big Query
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass