Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 777
  • Last Modified:

How to set primary key length for longtext column

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
dcayce
Asked:
dcayce
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
dcayceAuthor Commented:
That easy, huh? If the numeral there (100) represents the character count, is there a max allowable, or a limit?
0
 
dcayceAuthor Commented:
I just discovered there's a 1000 character limit. Is there a way to defeat that, to allow maybe 3000 characters?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no way (AFAIK)
0
 
dcayceAuthor Commented:
Alrighty, then. Thanks for Thanks for your help on this one.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now