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

Reply via email to