Rick,
sorry, it is not mentioned in the manual that MySQL performs a CREATE INDEX
by doing an ALTER TABLE. And ALTER TABLE has the feature (= documented bug)
that it removes foreign key definitions.
You should define all your indexes within the table create statement, like
in:
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id))
TYPE=INNODB;
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB
-----Original Message-----
From: Rick Flower <[EMAIL PROTECTED]>
To: MySQL Mailing List <[EMAIL PROTECTED]>
Cc: Heikki Tuuri <[EMAIL PROTECTED]>
Date: Friday, March 01, 2002 2:32 AM
Subject: Innodb tables lose foreign keys after creating an index...
>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
