> > Waynn Lue wrote: > >> As a side note, FKs do enforce other table specific properties like >> indexes on the fields being constrained, so they do add value there as >> well. But there's of course an extra cost on updates and inserts to >> see if the FK is violated. >> > > On the external table? No they don't. > > mysql> create table t1(id int primary key, name varchar(255)) > engine=innodb; > Query OK, 0 rows affected (0.00 sec) > > mysql> create table t2(t2id int, t1id int references t1(id)) engine=innodb; > Query OK, 0 rows affected (0.00 sec) > > mysql> show create table t2\G > *************************** 1. row *************************** > Table: t2 > Create Table: CREATE TABLE `t2` ( > `t2id` int(11) default NULL, > `t1id` int(11) default NULL > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 > 1 row in set (0.00 sec) > > > No auto-index on t2(t1id) at all. You have to define that yourself - you > might want it part of a multi-column index for example. > > You definitely should index it, but it won't happen automatically. > Hm, that's weird. Which version of mysql are you using? According to http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html: InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the *first* columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) *index_name*, if given, is used as described previously.
