SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast wiseguy316's Avatar
    Join Date
    Apr 2002
    Location
    Crown Point, IN
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy creating table in MySQL

    I don't understand something (ok, several things). I tried to create a simple table with two columns (a name and a number). However, I couldn't get it, so I tried using phpmyadmin to do it. When I did that, I got an error. Here is what was generated by phpmyadmin and the error message I got:

    SQL-query:
    CREATE TABLE `people` (`name` TEXT NOT NULL, `number` INT UNSIGNED NOT NULL , PRIMARY KEY (`name`), UNIQUE (`name`))

    MySQL said:
    BLOB column 'name' used in key specification without a key length
    I wasn't sure what that meant, so I put in a number for length and this is what I got:

    SQL-query:
    CREATE TABLE `people` (`name` TEXT(35) NOT NULL, `number` INT UNSIGNED NOT NULL , PRIMARY KEY (`name`), UNIQUE (`name`))

    MySQL said:
    You have an error in your SQL syntax near '(35) NOT NULL, `number` INT UNSIGNED NOT NULL , PRIMARY KEY (`name`), UNIQUE (`n' at line 1
    So...did I just overlook something? Any help would be greatly appreciated. Thanks in advance.

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2002
    Location
    UK
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a couple of things to think about:

    first,
    if you want to use a TEXT or BLOB column as an index you need to specify the length of the index.
    e.g. 'PRIMARY KEY (name(20))' uses the first 20 characters as the index.

    second,
    PRIMARY KEY creates a unique index with a NOT NULL constraint.
    UNIQUE creates a unique index that allows nulls.

    so in this case PRIMARY KEY will do.

    you can read more about CREATE TABLE syntax here and creating tables here


    something like this should work.
    Code:
    CREATE TABLE people (name TEXT NOT NULL, number INT UNSIGNED NOT NULL, PRIMARY KEY (name(50)));
    generally though i'd suggest using something like an AUTO_INCREMENT column to act as your primary key.
    just a personal preference though.

    hope this helps
    Last edited by martm; May 13, 2002 at 01:40.

  3. #3
    SitePoint Enthusiast wiseguy316's Avatar
    Join Date
    Apr 2002
    Location
    Crown Point, IN
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, thanks. That explains it much better than I could ever find anywhere else. I actually almost had it, but I used your example to figure out that apparently my host has an outdated version of phpmyadmin, which formats it a little differently, so I did it by hand and got it. Thanks a lot!

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using a character field for primary keys can be acceptable, however in this case I am wondering why you are using a text column. text is used for character strings over 255 characters in length. If you are not allowing/expecting strings that long, use VARCHAR( xxx ) (where XXX is an integer from 1 to 255).

  5. #5
    SitePoint Enthusiast wiseguy316's Avatar
    Join Date
    Apr 2002
    Location
    Crown Point, IN
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, thanks. I didn't really know what my options were.


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
  •