Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to set primary key length for longtext column

Posted on 2009-05-19
6
Medium Priority
?
786 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 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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 Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

606 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