I’m pretty sure foreign keys only work with Innodb tables, so you’d have to change your db to use Innodb rather than MYISAM, if you haven’t already (if that’s possible). Innodb support was added in Sept (according to the newsletter), but doesn’t seem to work on the database machine I’m on (I thought support was still in the works).
To do this, you’d do:
mysql> ALTER TABLE foo ENGINE=innodb;
(or go to “engines” in phpmyadmin). Neither works for me on the database machine at least one of my dbs is on.
It looks like at least some versions of PHPmyadmin have support for foreign keys; I’m not personally familiar much with phpmyadmin, but I think you should be able to do this, with Innodb enabled.
You could use integers for the column and then do a join when you select - not sure if that would work for your application, but it might do.
show create table foo;
thingie varchar(40) NOT NULL default ‘’,
value smallint(5) unsigned NOT NULL default 0,
PRIMARY KEY (
show create table values;
id smallint(5) NOT NULL auto_increment,
name varchar(10) NOT NULL default ''
PRIMARY KEY (
then you can do something like:
SELECT thingie,v.value FROM foo f LEFT JOIN values v on (f.value=v.id) WHERE thingie=“blah”;
or (to set “value” to “foo” if a result is not found):
SELECT thingie,ifnull(v.value,‘foo’)as value FROM foo f LEFT JOIN values v on (f.value=v.id) WHERE thingie=“blah”;
you could add new values in the “values” table as needed.