[nycphp-talk] are enums verboten?
smanes at magpie.com
Mon May 19 23:00:11 EDT 2003
Joshua S. Freeman wrote:
> For fields such as 'priority' or 'condition' I'm thinking of using type
Tagging on a late reply, enums are cool but they become unwieldy with
large sets. They're mainly useful for protecting against range
violations, which you can do just as easily in your application. But
mainly they're a poor substitute for foreign key constraints, or at
least I think so.
I haven't played with InnoDB's FK constraints yet but the general idea
is to create your restricted dataset in a reference table, indexed on a
unique ID. Then you reference that table from your main table whenever
you add a new row with a species_id you want to have first defined:
CREATE TABLE ref_species (
species_id INT NOT NULL,
PRIMARY KEY (id));
CREATE TABLE species (
id INT NOT NULL,
species_id NOT NULL,
... more columns ...
FOREIGN KEY (species_id) REFERENCES ref_species(species_id)
ON DELETE SET NULL) TYPE=INNODB;
(or something like that). What this does is prevent you from entering a
species_id into 'species' that wasn't predefined in 'ref_species'. This
should be a last line of defense though because it will throw a db error
if you try to do otherwise. Your application should sanity-check that
value before you ship it off to the database.
More information about the talk