creating table in MySQL

[wiseguy316]

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.

[martm]

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.

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

[wiseguy316]

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!

[MattR]

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).

[wiseguy316]

Alright, thanks. I didn't really know what my options were.