At 11:55 AM 6/25/2002 -0400, Andy Dustman wrote: > From a usability perspective, since other databases don't seem to be so >picky about this sort of thing, InnoDB ought to (IMHO): > >a) Act on REFERENCES clauses (i.e. column type REFERENCES ref-table) >instead of ignoring them. > >b) Auto-create indexes on the foreign keys (but not the referenced keys). >It does this for UNIQUE columns already. > >c) The referenced key column name ought to be optional, defaulting to the >primary key of the referenced table. See example below.
I agree with you regarding points a) and b), as a matter of moral oughtness, but I can tell you from frustrating personal experience that Oracle, like MySQL, does not auto-create indexes on foreign keys. But, unlike MySQL, Oracle does not require that the foreign key column be indexed. So there is no syntactical error in Oracle. Your code runs, but your queries run slower than molasses in January, and it is up to you to figure out why. Totally the opposite of helpful. I think InnoDB's approach is slightly more helpful, in that it prevents self-sabotage, but it would surely be more helpful still, if it went ahead and created the index, as you suggest. SMOP, AFAIK. Regarding c), the referenced key column name is optional, and works as you say it does in Oracle. I'm not sure that is the way it ought to be. It opens up the perverse possibility that the foreign key column is given the same name as a column of the parent table _other than_ the parent primary key--say, an obsolete key left over from an earlier numbering system, or a crossover key to some other manufacturer's equivalent product, or maybe just a perversely dumb choice of column names: whatever. So your foreign key ends up pointing somewhere other than where you intended. Better to throw a syntax error (albeit with a perfectly clear message) in this case, and require the developer to be specific. Just my $0.02. --Erv --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
