NYCPHP Meetup

[Steve Manes]

Joshua S. Freeman wrote:
> For fields such as 'priority' or 'condition' I'm thinking of using type
> 'enum':

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,
	description VARCHAR2(100),
	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.