?
Solved

How to set primary key length for longtext column

Posted on 2009-05-19
6
Medium Priority
?
771 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 46

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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

718 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