Solved

How to set primary key length for longtext column

Posted on 2009-05-19
6
745 Views
Last Modified: 2012-05-07
Experts;

I'm trying to build an SQL table with one element, a 'Description' column, which has to be tagged as longtext. When I try to load the database I'm getting an 1170 error telling me the Primary Key needs a key length specified. What would be the solution to this?

Yeah, I know it sounds weird... but I'm experimenting.

Thanks.

Cayce
CREATE TABLE IF NOT EXISTS `Items` (
  `Description` longtext,
  PRIMARY KEY  (`Description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `Items`
--
 
INSERT INTO `Items` (`Description`) VALUES
 
('Long Description text goes here');

Open in new window

0
Comment
Question by:dcayce
  • 3
  • 2
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24426426
this should do:
CREATE TABLE IF NOT EXISTS `Items` (
  `Description` longtext,
  PRIMARY KEY  (`Description` (100) )
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Open in new window

0
 

Author Comment

by:dcayce
ID: 24427472
That easy, huh? If the numeral there (100) represents the character count, is there a max allowable, or a limit?
0
 

Author Comment

by:dcayce
ID: 24427518
I just discovered there's a 1000 character limit. Is there a way to defeat that, to allow maybe 3000 characters?
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24429032
no way (AFAIK)
0
 

Author Closing Comment

by:dcayce
ID: 31583229
Alrighty, then. Thanks for Thanks for your help on this one.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24431696
Hi dcayce,

Just to add a bit to the discussion...

Using text as the primary key isn't always the best solution.  Using longtext as the primary key is perhaps never the correct solution.

An index makes a copy the data being indexed in the index pages, attaches some internal data items (like source data pointer), and organizes it in a sorted structure.  An poorly designed table and index can result in the index being as large (or larger) than the original table.

One way around the limitation is to create your table so that it has an identity (auto_increment) column as the primary key, a longtext value for your actual data, and a smaller field that can be indexed and examined.  The smaller field could be the first 50 (or 100) characters of the text, an MD5 checksum, or most anything else that makes sense to your application.

It would add a level of nuisance when searching as you'd have to compare both the short value and the long string, but it does work quite well.

--pseudo code follows:

CREATE TABLE mytable (
  id  auto_increment,
  short_text varchar (50),
  text varchar (3000),
  primary key id
);

CREATE INDEX idx0001 ON mytable (short_text);

INSERT INTO mytable (text, short_text) VALUES ('some very, very, very long string', substr (1, 50, 'some very, very, very long string');

CREATE VIEW findtext (search_string varchar (3000))
AS
RETURN
  SELECT ID
  FROM mytable
  WHERE short_text = substr (search_string, 1, 50)
  AND text = search_string;


The index is built on 50 character strings so it's small and fast.  The SELECT does an index lookup.  A data page is read for every matching row in the index.  A small price to pay for being able to effectively index the data.


Good Luck,
Kent
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question