Solved

How to set primary key length for longtext column

Posted on 2009-05-19
6
767 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 143

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 143

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:Kent Olsen
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

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

623 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