Chris Hope wrote:
Peter Scott wrote:
I created a table that has a column in that needs to contain a full
Unix file path. Since 2048 was too long for a VARCHAR, I made it
TEXT. I since populated the table. Now I want to make the path
column a primary key, and I can't figure out how. (I googled the
web and groups without luck, looked over the reference manual also,
especially reading the entry on BLOBs.)
I was able to make a fulltext index with:
create fulltext index path_idx on the_table (path);
But my attempts to make a primary key have failed:
SQL> alter table the_table add primary key (path);
BLOB column 'path' used in key specification without a key length
SQL> alter table the_table add primary key (path(2048));
Incorrect sub part key. The used key part isn't a string, the used
length is longer than the key part or the table handler doesn't
support unique sub keys
SQL> alter table the_table add constraint unique (path);
BLOB column 'path' used in key specification without a key length
Actually, having made the index, all I care about is the unique
constraint. How do I do that?
Pretty simple answer. You can't make a text column a primary key. The
only sort of index you can put on a text column is full text.
A quick look in the manual and I stand corrected. However it doesn't
specify whether or not you can make them unique or primary, but you an
always try. Just make sure the maximum prefix length is 255 or 1000
characters depending on your version and table type as specified below.
From
http://dev.mysql.com/doc/mysql/en/indexes.html :
The MyISAM and (as of MySQL 4.0.14) InnoDB storage engines also support
indexing on BLOB and TEXT columns. When indexing a BLOB or TEXT column,
you must specify a prefix length for the index. For example:
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
Prefixes can be up to 255 bytes long (or 1000 bytes for MyISAM and
InnoDB tables as of MySQL 4.1.2). Note that prefix limits are measured
in bytes, whereas the prefix length in CREATE TABLE statements is
interpreted as number of characters. Take this into account when
specifying a prefix length for a column that uses a multi-byte
character set.
As of MySQL 3.23.23, you can also create FULLTEXT indexes. They are used
for full-text searches. Only the MyISAM table type supports FULLTEXT
indexes and only for CHAR, VARCHAR, and TEXT columns. Indexing always
happens over the entire column and partial (prefix) indexing is not
supported. See Section 12.6, ?Full-Text Search Functions? for details.
--
Chris Hope - The Electric Toolbox -
http://www.electrictoolbox.com/