Hi! Since InnoDB implements foreign keys at a low level, it cannot presently instruct MySQL to change the table definition in the .frm file and create a new index.
We can modify the MySQL parser though so that it will treat FOREIGN KEY (...) REFERENCES ... like it does an ordinary KEY declaration. That would still leave it to the user to make sure there is an index in the REFERENCED table. The change in the parser looks almost trivial. A table handler should also be able to return a string as an error message. I forward this email to Peter Zaitsev who can look how easy it is to change MySQL-4.1 to return more diverse error messages. Regards, Heikki Innobase Oy ----- Original Message ----- From: "Erv Young" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, June 26, 2002 1:19 PM Subject: Re: Can't create FOREIGN KEY restraints on InnoDb tables > 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 > --------------------------------------------------------------------- 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
