SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2002
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    HOW : Making TEXT as Primary Key

    Hello all ,

    i have a problem when i try to create a table in my Database

    i have two fileds :

    1- link : primary key , have to be more than 255 characters ( so i have to use TEXT family (tiny,long,...) )

    2- path : not primary , and does not have to be more than 255 chars .

    my problem is in 'link'

    how can i create a TEXT field as a primary key , when i try i have two problems situations as follows :

    1) when i did not specify the length of this filed i get this msg :

    BLOB column 'link' used in key specification without a key length

    and SQL was as follows :

    Code:
     
     CREATE TABLE `cat2` (  
     `link` TEXT NOT NULL , 
     `path` VARCHAR( 200 ) NOT NULL , 
     PRIMARY KEY ( `link` )  
     )


    2) when i specify the length of this filed i got this msg :


    #1064 - You have an error in your SQL syntax near '(500) NOT NULL, `path` VARCHAR(200) NOT NULL, PRIMARY KEY (`link`))' at line 1

    and SQL was :

    Code:
     
     CREATE TABLE `cat2` (  
     `link` TEXT( 500 ) NOT NULL ,
      `path` VARCHAR( 200 ) NOT NULL , 
     PRIMARY KEY ( `link` )
     )

    also i try FULTEXT , with no changes !

    please help me

    NOTE : i use phpMyAdmin to build the databases and tables .

    thanks

  2. #2
    SitePoint Addict Knightime's Avatar
    Join Date
    Jan 2003
    Location
    USA
    Posts
    376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    P.H.P. refer to this thread in a post in the MySql forum. You should get a quicker reply there.
    Troy

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Parry Sound, ON
    Posts
    725
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why would you ever want a text field as a primary key? Can you explain it?

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2002
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Knightime , i will do , thanks .

    Hardcoded , because i want to insert links ( Hyper Links ) which is sometimes more than 255 character ( which is the max allowed in VARCHAR and CHAR )

    and this links have to be unique , so i want it as primary Key , because i will deal with it in my program ( Spider with a Complete Search engine )

    thanks
    vbmenu_register("postmenu_1632357", true);

  5. #5
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    you have to specify the length in your key definition, not in your column definition, e.g.

    Code:
    CREATE TABLE cat2 (  
      link TEXT NOT NULL ,
      path VARCHAR( 200 ) NOT NULL , 
     PRIMARY KEY ( link(500) )
     )
    see http://dev.mysql.com/doc/mysql/en/create-index.html:

    BLOB and TEXT columns also can be indexed, but a prefix length must be given.

    The statement shown here creates an index using the first 10 characters of the name column:

    CREATE INDEX part_of_name ON customer (name(10));
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  6. #6
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    Germany
    Posts
    550
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think using a text field as a primary key will be kind of ineffective, why not just add an extra column, with integer type and using this as your primary key? you could make the text field unique, so you won't get duplicates

  7. #7
    SitePoint Enthusiast
    Join Date
    Aug 2002
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i try your solution kleineme , but i got this msg :

    #1089 - 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

  8. #8
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    sorry, I didn't test the SQL before posting it. Now I did and apparently the key length can be no longer than 255.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  9. #9
    SitePoint Enthusiast
    Join Date
    Aug 2002
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So why MySQL does not support TEXT Field to be primary Key ?

    it is really strange !

  10. #10
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    US
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think hyperlink with 255 is more than enough in 99.99% of the case

  11. #11
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    you can use TEXT columns as a (primary) key but you have to limit the number of siginficant characters, which should indeed be enough to produce a unique entry, as sonyc said. But remember that it's the maximum for one single column. You can use compound keys with two or more columns to overcome the 255 char restriction. But generally such a limit for a single column makes sense. Say you have a database with thousands of entries in it. Now imagine that you create a complete 64KB TEXT column key - wouldn't that be a complete performance overkill?
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  12. #12
    SitePoint Enthusiast
    Join Date
    Aug 2002
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello Sonyc

    99.9 % is not enough for me , i need it 100 % , because i am going to build a complete search engine (Spider , indexer , ... ) with automatic classification , so this big project have to complete , have to be 100 % complete


    dear kleineme , you said that i can create a TEXT field as primary key , but you did not tell me how ? i mean in SQL speaking .

    can you give me the SQL that solve this problem !

    thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •