473,706 Members | 2,064 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trying to make TEXT column PRIMARY KEY

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

Jul 23 '05 #1
4 25118
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.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 23 '05 #2
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/
Jul 23 '05 #3
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.
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.
Actually, having made the index, all I care about is the unique constraint.
How do I do that?


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.
Jul 23 '05 #4
In article <ct*********@en ews4.newsguy.co m>,
Bill Karwin <bi**@karwin.co m> writes:
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. [snip] Actually, having made the index, all I care about is the unique constraint.
How do I do that?


One solution might be to make a digest of the long string, using the
MD5() or SHA() functions.

[snip]

Dang, I was afraid of that. Thanks.

--
Peter Scott
http://www.perldebugged.com/
*** NEW *** http://www.perlmedic.com/
Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
36771
by: Ken1 | last post by:
I am going to drop a primary key from one column and create a new column to be used as primary key in an existing database. The old column was a date column which someone earlier though was a good candidate for a primary key which we all know it's not. Now I want to add a new field, i.e. called ID, with a normal number sequence as primary key. I have dropped the primary key, created the new column, created the new sequence and created...
4
1791
by: Mavis Tilden | last post by:
Hi all, So I've been reading the newsgroups, and reading a few books trying to learn SQL and SQL Server 2000. The books tell me I need a Primary Key, and that every table should have one. I know (I think) that a Primary Key is a special field that uniquely identifies each record or row within a table. My question is this: If I have a field or column whose values are all (and will be) different in every row, is that what a Primary Key...
9
13027
by: kojim | last post by:
Test page: http://www.key-horse.com/linkt.html I have a td that's styled to look something like a button, and on that button is anchor text. So far, I can't make the entire "surface" of the button be a part of the anchor. That is, I would like to click on /any/ place on the td/button surface and take the link. As it is now, the only clickable area is in the text itself.
3
5844
by: Danny | last post by:
Hi again I am trying to import from an excel spreadsheet into access 2002, but the order that is in the spreadsheet is not preserved when I import. using DoCmd.TransferSpreadsheet in code. Access sets indexes on certain fields with "Code" in the field. Even if I remove these index, the order is close, but still some of the fields are out of order.
3
4044
by: google | last post by:
This is something I've done plenty of times in '97, but I can't seem to get it to work correctly in Access 2003. Say, for example, I have a form with an unbound combobox, the data source is a table of customers, first column is the key field, which is hidden with a 0" width, and the second column is the customer name. The SQL is a union query that also inserts Null in the first column, and "All" in the second column. That combobox gets...
8
9239
by: Mike Wertheim | last post by:
Hi, I'm using PostgreSQL 8. I have two tables that I am doing a join on, and the join executes very slowly. The table called Notification has a text field called NotificationID, which is its primary key. The Notification table also has an int4 field called ItemID, and it has an index on the ItemID field. The table
0
1639
by: Henry | last post by:
I am trying to create a TreeView control that works with an ADO Dataset DataTable or the new BindingSource stuff in .NET 2.0 to build a Treeview that is populated. This is what I came up with so far... I can't run it yet because I don't have the DataTable source... I still want to get some sort of assessment as to whether I am heading in the right direction. Here is the code: ================================ using System;
5
3786
by: Learner | last post by:
Hello, Here is the code snippet I got strucked at. I am unable to convert the below line of code to its equavalent vb.net code. could some one please help me with this? static public List<RoleData> GetRoles() { return GetRoles(null, false); }
6
1827
by: pbd22 | last post by:
Hi. I am trying to insert a zero into the below table and I am being told that: Cannot insert the value NULL into column 'UsageToday', table DB.dbo.Pub_Count'; column does not allow nulls. UPDATE fails. The statement has been terminated. What I am trying to insert is a string that I convert into an int
0
8783
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9287
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9056
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6615
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4454
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4710
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3150
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2503
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2096
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.