Hi! Use SELECT INTO OUTFILE + READ DATA INFILE
Do not rename tables. Regards, Heikki -----Original Message----- From: Philip Molter <[EMAIL PROTECTED]> To: Heikki Tuuri <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Thursday, January 03, 2002 5:54 PM Subject: Re: FOREIGN KEYs and ALTER TABLE >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
