SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I'm trying to create a table to store my members data. The first column is the username. The username of the members must be unique, so I tried to use the following lines:

    mysql> create table users (
    -> username text not null primary key,
    ....

    But I'm getting the following error message:

    ERROR 1073: BLOB column 'username' can't be used in key specification with the used table type

    What does this mean?


  2. #2
    SitePoint Wizard jonese's Avatar
    Join Date
    Jul 1999
    Location
    Powder Springs GA (Atlanta)
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    instead of "text" try using the "varchar" instead.

    If you get an error you might need to specify how many char the varchar can be. I think the limit is 225 but i'm not sure of the syntax to use in mySQL.

    ------------------
    Eric Jones - SitePoint Administrator
    http://www.sitepoint.com
    eric@sitepoint.com

  3. #3
    exit();
    Join Date
    Jul 2000
    Posts
    759
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually the limit is 255

    So do:

    VARCHAR(255)

    ------------------
    WebDevHQ.com - The Web Developer Headquarters

  4. #4
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    While the above messages are correct in that a VARCHAR column may be used as an index (e.g. the primary key) while a TEXT column may not, you should rethink this choice.

    The primary key should always be a column that contains no actual data, other than that necessary to distinguish entries from each other. Typically, an auto-incrementing integer (INT AUTO_INCREMENT) fits the bill nicely.

    As for enforcing uniqueness of your usernames, this can be done by creating a UNIQUE index on that column:

    CREATE TABLE (
    ID AUTO_INCREMENT NOT NULL PRIMARY KEY,
    UserID VARCHAR(255) NOT NULL,
    UNIQUE (UserID),
    ...
    );


    ------------------
    -Kevin Yank.
    http://www.SitePoint.com/
    Helping Small Business Grow Online!

    [This message has been edited by kyank (edited July 30, 2000).]

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you da man, kevin.

  6. #6
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    you da bird, wimpypenguin.

    ------------------
    -Kevin Yank.
    http://www.SitePoint.com/
    Helping Small Business Grow Online!


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
  •