Trying to make TEXT column PRIMARY KEY

[Peter Scott]

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?

--
Peter Scott

[Chris Hope]

Re: Trying to make TEXT column PRIMARY KEY

Peter Scott wrote:
[color=blue]
> 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?[/color]

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.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Chris Hope]

Re: Trying to make TEXT column PRIMARY KEY

Chris Hope wrote:
[color=blue]
> Peter Scott wrote:
> [color=green]
>> 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?[/color]
>
> 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.[/color]

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/

[Bill Karwin]

Re: Trying to make TEXT column PRIMARY KEY

Peter Scott wrote:[color=blue]
> 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.[/color]

BLOB and TEXT columns can be indexed, but only by using an index prefix
length. That is, only a leading part of uniform length of a BLOB or
TEXT can be used for the index. In MySQL 4.1.2, the prefix can be up to
1000 characters; in earlier versions, the limit is 255 characters.

Read this page for more information:

http://dev.mysql.com/doc/mysql/en/indexes.html

Since you are using this column to store pathnames, it's likely that
there will be a lot of duplication in the leading portion of the
strings. This probably makes it impractical to use for enforcing
uniqueness.
[color=blue]
> Actually, having made the index, all I care about is the unique constraint.
> How do I do that?[/color]

One solution might be to make a digest of the long string, using the
MD5() or SHA() functions. The digest is a string of 32 or 40 characters
respectively, regardless of the length of the input string. The value
is very likely to be unique and reproducable based on the input string.
Such a digest value is suitable for use in a unique index, and can be
used to enforce uniqueness of the longer string input as well, to the
extent that each input string results in a unique digest value.

Regards,
Bill K.

[Peter Scott]

Re: Trying to make TEXT column PRIMARY KEY

In article <ctmde201cq6@en ews4.newsguy.co m>,
Bill Karwin <bill@karwin.co m> writes:[color=blue]
>Peter Scott wrote:[color=green]
>> 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.[/color][/color]
[snip][color=blue][color=green]
>> Actually, having made the index, all I care about is the unique constraint.
>> How do I do that?[/color]
>
>One solution might be to make a digest of the long string, using the
>MD5() or SHA() functions.[/color]
[snip]

Dang, I was afraid of that. Thanks.

--
Peter Scott

http://www.perldebugged.com/

*** NEW *** http://www.perlmedic.com/