On Thu, Jan 03, 2002 at 05:21:49PM +0200, Heikki Tuuri wrote: : 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)
Well, the problem right here with this methodology is that if parent is a large table, you have to reinsert all the data back into that table. That may be fine for small tables, but for 100,000 to 1,000,000+ row tables, that's pretty annoying. So we make a copy of our data first, so that the reinsertion goes much easier. I do this by simply renaming the table to get it out of the way. Then I make my new table with the same name as my old one, insert into it out of my old (renamed) table and then drop the renamed. Thus, I'm sure my data is correct. The problem appears to be that the child foreign key follows it's parent table around even after the rename, so when I create my new table with the same name as the old one, the child is no longer pointing there. On first thought, I would expect the child not to follow on a rename (because of the explicit pointer to a table named 'parent'). On the flip-side, I can see how it might be beneficial, but foreign keys in the abstract sense are references to keys by table names, not references to keys by table object. Using this methodology (listed below) should be significantly faster if you want to preserve the data in your table. Using the method you detailed, if you wanted to preserve the contents of the table being altered, you'd first have to dump your data into a file or another temporary table, then reinsert it. As an aside, the reverse method of this (create the new table, populate it with data from the old table, drop the old table, rename the new table) also fails on a foreign key constraint. mysql> create table parent( id INT NOT NULL, PRIMARY KEY (id)) TYPE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> create table child( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)); 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 child values (5, 10); Query OK, 1 row affected (0.00 sec) mysql> alter table parent rename parent_temp; Query OK, 0 rows affected (0.02 sec) mysql> create table parent( id INT NOT NULL, id2 INT NOT NULL, PRIMARY KEY (id)) TYPE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> insert into parent select id, 0 FROM parent_temp; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> drop table parent_temp; Query OK, 0 rows affected (0.00 sec) mysql> insert into parent values (20, 0); Query OK, 1 row affected (0.00 sec) mysql> insert into child values (6,20); ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql> select * from parent; +----+-----+ | id | id2 | +----+-----+ | 10 | 0 | | 20 | 0 | +----+-----+ 2 rows in set (0.00 sec) mysql> select * from child; +------+-----------+ | id | parent_id | +------+-----------+ | 5 | 10 | +------+-----------+ 1 row in set (0.00 sec) * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] --------------------------------------------------------------------- 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
