Primary Key datatype = varchar

[sfh]

Greetings all,

(I had posted this in MS SQL on accident, my apologies :( )

I have a question concerning primary key types.

In the past, I have always created tables with a primary key as an "int" such as:

[COLOR=Blue][FONT=Courier New]CREATE TABLE color_id (
color_id int(10) unsigned NOT NULL auto_increment primary key,
color varchar(45) default NULL
) TYPE=INNODB DEFAULT CHARSET=latin1; [/FONT] [/COLOR]

But as of late I have been creating rather large databases that have many lookup tables.

Here is a quick example database I just created:

[COLOR=Blue][FONT=Courier New]mysql> select * from color_id;
+----------+--------+
| color_id | color |
+----------+--------+
| 1 | Red |
| 2 | Green |
| 3 | Blue |
| 4 | Purple |
| 5 | Yellow |
| 6 | Black |
| 7 | Grey |
+----------+--------+[/FONT][/COLOR]

That would be just a lookup field. and this would be a table with the foreign keys:

[COLOR=Blue][FONT=Courier New]mysql> select * from name_id;
+---------+----------+-------+-------+
| name_id | name | color | shape |
+---------+----------+-------+-------+
| 1 | Jim | 4 | 6 |
| 2 | Scott | 6 | 5 |
| 3 | Cory | 1 | 6 |
| 4 | Jim | 2 | 3 |
| 5 | Janet | 2 | 1 |
| 6 | Bobby | 1 | 6 |
| 7 | Carlos | 3 | 3 |
| 8 | Peter | 6 | 3 |
| 9 | Paul | 7 | 3 |
| 10 | Mary | 3 | 4 |
| 11 | Blair | NULL | NULL |
| 12 | Johnny | NULL | NULL |
| 13 | Mike | 2 | NULL |
| 14 | Morgan | NULL | 5 |
| 15 | Christos | 3 | 3 |
+---------+----------+-------+-------+ [/FONT] [/COLOR]

Ive found this difficult to work with.

Two examples programs are a csv insert/update program I have that is not lookup field aware so it doesent work on tables like this.

Another example is a a front end php program that has some nice sorting featres on the columns, but unfortunately it sorts on the field (key value) as opposed to the looked up value!?!?!

So for my question, What are the issues / drawbacks on creating (on lookup tables) my primary keys as varchar(45)? I know there might be index performance issues, but if every entry in these tables is unique, are there any other reasons for not doing this?

Here is an updated .sql file to represent what I am talking about:

[COLOR=Blue][FONT=Courier New]CREATE TABLE color (
color varchar(45) NOT NULL primary key
) TYPE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE shape (
shape varchar(45) NOT NULL primary key
) TYPE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE name (
name varchar(45) NOT NULL primary key,
color varchar(45) default NULL,
shape varchar(45) default NULL,
INDEX color_ind (color),
INDEX shape_ind (shape),
FOREIGN KEY (color) REFERENCES color(color) ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY (shape) REFERENCES shape(shape) ON DELETE SET NULL ON UPDATE CASCADE
) TYPE=INNODB DEFAULT CHARSET=latin1;[/FONT][/COLOR]

Thanks all, take care!

[sfh]

Nevermind....

Nevermind,

Completely forgot that the PK cant be edited, completely nullifying the point of an RDBMS....

Sorry....