On Primary Keys and phpMyAdmin 3.0

[Guest]

Hello all,

Im using phpMyAdmin to create my database and I'm a bit vexed with regards to assigning primary keys.

In version 3, when I'm defining fields in my tables, over second column to the right is a drop-down menu for Index. If I leave it alone, or if I use it to select Primary from the menu (Primary, Unique, Index, Fulltext) I end up with Primary highlighted when viewing the structure of the table. In fact, of the three variables I am using, INT, VARCHAR and TEXT, fields using INT and VARCHAR are showing up in the structure as primary. Only the text firld does not.

Is this right, or should I be choosing Index or Unique for the INT and VARCHAR defined fields that are not my primary key?

Thanks in advance!

Sincerely,

wordman

[David_Powers]

The primary key is a unique identifier for a record in a database table. In normal circumstances, only one field (column) should be designated as the primary key.

The normal practice is to set the first column as the primary key, using INT as the data type, set Attributes to UNSIGNED, Index to Primary, and select AUTO_INCREMENT.

Using UNSIGNED prevents the use of negative numbers, thereby doubling the capacity of the column. AUTO_INCREMENT automatically assigns the next available number as the record's primary key.

A table can have only one primary key. It sounds as though you have set your table definition to use a joint primary key. This is used only for something like an association table. Using an index on other columns should be done only when you need to search the column on a frequent basis. Otherwise, it actually degrades the performance of your database by slowing down the insertion of new records.

Text columns cannot be indexed, except by using a FULLTEXT index, which uses a different type of SQL query to search for text.

[Guest]

David,

Thank you for that. What was throwing me off was not setting the index manually. phpMyAdmin uses a pulldown menu for the index options, and the default value is Primary. What's confusing is trying to go back to change the other colums to Index. the screen shows the relevant index icons activated but clicking them doesn't seem to change anything. This will be a bummer if I have to recreate the tables as several of them already have data in them.

As a personal preference, do you choose to use Varchar as opposed to Text (especially since Varchar can be set to contain as much text as Text)? As I'm still new with this, I want to adopt good practices.

Thank you, as always, for your help!

Sincerely,

wordman