Hi! -----Original Message----- From: Philip Molter <[EMAIL PROTECTED]> To: Heikki Tuuri <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Thursday, January 03, 2002 3:21 PM Subject: Re: FOREIGN KEYs and ALTER TABLE
>On Thu, Jan 03, 2002 at 01:48:21PM +0200, Heikki Tuuri wrote: >: Hi! >: >: This is a feature (= documented bug). Look at >: http://www.innodb.com/ibman.html: >: ... >: Updated December 13, 2001: Added a note that you should not do an ALTER >: TABLE to a table which has or is referenced in a foreign key constraint, but >: use DROP TABLE + CREATE TABLE to modify the schema > >Hrmm. Is that a "feature" that's planned to be fixed. Obviously, >if you have a table with thousands or millions of rows in it, ALTER >TABLE is a lot easier than copying the table to a temp table, >dropping the original table, creating a new table, and copying the >data back in. No timetable set yet. But note that some ALTER TABLEs actually recreate the table, rename the old, and drop the old.. Exactly that is the reason why ALTER TABLE of the referenced table can confuse the constraints referring to it. >Also, we've been using the DROP/CREATE methodology, and keys in >other tables referencing the altered table fail (they don't stop >working, they refuse to take entries, citing a failed foreign key >constraint). We have to DROP/CREATE every table in the key chain >to get it done properly. Is that how that's supposed to work? No, DROP + CREATE should do the job. I tested it below and it seemed to work. But while the dropped table is non-existent, or does not contain the required rows, foreign key constraints referencing that table will fail. ...... heikki@donna:~/mysqlt/client > mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.47 Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql> CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; Query OK, 0 rows affected (0.11 sec) mysql> CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), -> FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INN ODB; Query OK, 0 rows affected (0.03 sec) mysql> insert into parent values (10); Query OK, 1 row affected (0.00 sec) mysql> insert into child values (5, 10); Query OK, 1 row affected (0.00 sec) mysql> drop table parent; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql> insert into parent values (10); Query OK, 1 row affected (0.00 sec) mysql> insert into parent values (20); Query OK, 1 row affected (0.01 sec) mysql> insert into child values (6, 20); Query OK, 1 row affected (0.00 sec) mysql> drop table child; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), -> FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INN ODB; Query OK, 0 rows affected (0.00 sec) mysql> insert into child values (6, 20); Query OK, 1 row affected (0.00 sec) mysql> delete from parent where id = 20; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails mysql> delete from parent where id = 10; Query OK, 1 row affected (0.00 sec) mysql> insert into child values (5, 10); ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql> mysql> create table child2 (id INT NOT NULL, parid1 INT, parid2 INT, index(parid 1), index (parid2), primary key (id), foreign key (parid1) references parent(id) , foreign key (parid2) references parent(id)) type = innodb; Query OK, 0 rows affected (0.02 sec) mysql> show table status from test like 'child2'; +--------+--------+------------+------+----------------+-------------+------ ---- -------+--------------+-----------+----------------+-------------+---------- ---+ ------------+----------------+---------------------------------------------- ---- --------------------------------------+ | 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 | +--------+--------+------------+------+----------------+-------------+------ ---- -------+--------------+-----------+----------------+-------------+---------- ---+ ------------+----------------+---------------------------------------------- ---- --------------------------------------+ | child2 | InnoDB | Fixed | 0 | 0 | 16384 | NULL | 32768 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 130048 kB; (parid1) REFER test/paren t(id); (parid2) REFER test/parent(id) | +--------+--------+------------+------+----------------+-------------+------ ---- -------+--------------+-----------+----------------+-------------+---------- ---+ ------------+----------------+---------------------------------------------- ---- --------------------------------------+ 1 row in set (0.00 sec) mysql> select * from parent; +----+ | id | +----+ | 20 | +----+ 1 row in set (0.00 sec) mysql> insert into child2 values (1, 20, 20); Query OK, 1 row affected (0.00 sec) mysql> insert into child2 values (2, 10, 20); ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql> insert into child2 values (2, 10, 20); ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql> insert into child2 values (2, 20, 10); ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql> drop table parent; Query OK, 0 rows affected (0.01 sec) mysql> insert into child2 values (5, 20, 20); ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql> CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql> insert into parent values (20); Query OK, 1 row affected (0.00 sec) mysql> insert into child2 values (5, 20, 20); Query OK, 1 row affected (0.00 sec) mysql> ........ >* Philip Molter >* Texas.net Internet >* http://www.texas.net/ >* [EMAIL PROTECTED] 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 --------------------------------------------------------------------- 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
