Hi all.. Are any of you aware of a way to get indexes to work at all with Innodb tables containing foreign keys? I'm finding that after doing a "create index" on a table which *had* foreign keys, after the create, the foreign keys are gone.. I've included a simple test below which shows off the problem quite well.. I've searched around on Google to see if anyone had run into this problem, but didn't find any reference.. This really makes foreign keys worthless in MySQL.. The more I dig into MySQL, the less I like it due to missing features or wierd side effects of existing ones.. Perhaps someone can shed some light on what I may be doing wrong.. By the way, I'm using MySQL 3.23.47 on a Solaris box -- if it matters..
To reproduce the problem: 1) create table test_fk_parent(id int not null, primary key (id)) type=innodb; 2) create table test_fk_child ( id int not null unique, parent_id int, index par_ind(parent_id), foreign key(parent_id) references test_fk_parent(id)) type=innodb; 3) create index CHILD_KEY on test_fk_child (id); Below is the sample output : mysql> create table test_fk_parent(id int not null, primary key (id)) type=innodb; Query OK, 0 rows affected (0.04 sec) mysql> create table test_fk_child ( id int not null unique, parent_id int, index par_ind(parent_id), foreign key(parent_id) references test_fk_parent(id)) type=innodb; Query OK, 0 rows affected (0.05 sec) mysql> show table status; +----------------+--------+------------+------+----------------+----------- --+-----------------+--------------+-----------+----------------+---------- ---+-------------+------------+----------------+--------------------------- ------------------------------------------+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +----------------+--------+------------+------+----------------+----------- --+-----------------+--------------+-----------+----------------+---------- ---+-------------+------------+----------------+--------------------------- ------------------------------------------+ | test_fk_child | InnoDB | Fixed | 0 | 0 | 16384 | NULL | 32768 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 5807104 kB; (parent_id) REFER vista/test_fk_parent(id) | | test_fk_parent | InnoDB | Fixed | 0 | 0 | 16384 | NULL | 0 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 5807104 kB | +----------------+--------+------------+------+----------------+----------- --+-----------------+--------------+-----------+----------------+---------- ---+-------------+------------+----------------+--------------------------- ------------------------------------------+ 2 rows in set (0.01 sec) [[[ NOTICE THE EXISTING FOREIGN KEY ABOVE ]]] mysql> create index CHILD_KEY on test_fk_child (id); Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show table status; +----------------+--------+------------+------+----------------+----------- --+-----------------+--------------+-----------+----------------+---------- ---+-------------+------------+----------------+-------------------------+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +----------------+--------+------------+------+----------------+----------- --+-----------------+--------------+-----------+----------------+---------- ---+-------------+------------+----------------+-------------------------+ | test_fk_child | InnoDB | Fixed | 0 | 0 | 16384 | NULL | 49152 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 5807104 kB | | test_fk_parent | InnoDB | Fixed | 0 | 0 | 16384 | NULL | 0 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 5807104 kB | +----------------+--------+------------+------+----------------+----------- --+-----------------+--------------+-----------+----------------+---------- ---+-------------+------------+----------------+-------------------------+ 2 rows in set (0.00 sec) [[[ NOTICE THE NOW MISSING FOREIGN KEY ABOVE ]]] --------------------------------------------------------------------- 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
