PHPmyadmin and foreign keys

apps

#1

Afternoon everyone. I’ve recently set up a new database and am administering it via PHPmyadmin as you can probably guess. I’ve got a table with several columns, and would like one of the columns to only be able to contain values which can be located in a column in another table. In Oracle, I might do this with a List Of Values. I don’t really want to use an ENUM as the list will frequently change, and I’d rather apply the constraint at a database level, perhaps with the exchange of foreign keys. However, I have no idea how to do this with PHPmyadmin and I’ve spent several hours now trawling around trying to find out how to do this…

Any help would be much appreciated…


#2

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.

Something like:

show create table foo;
CREATE TABLE foo (
thingie varchar(40) NOT NULL default ‘’,
value smallint(5) unsigned NOT NULL default 0,
PRIMARY KEY (thingie),
)
show create table values;
CREATE TABLE values (
id smallint(5) NOT NULL auto_increment,
name varchar(10) NOT NULL default ''
PRIMARY KEY (id),
UNIQUE KEY value (value)
)

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.