David Blomstrom <[EMAIL PROTECTED]> wrote: > --- "Robert A. Rosenberg" <[EMAIL PROTECTED]> wrote: >> At 20:36 -0700 on 06/02/2004, David Blomstrom wrote >> about >> Exporting/Importing Databases: >> >> >Third, my practice database includes half a dozen >> >tables, but only one made it online, followed by >> this >> >error message: >> > >> >Database USERNAME running on localhost >> > >> >Error >> > >> >SQL-query : >> > >> >CREATE TABLE `counties` ( >> >`SCode` varchar( 6 ) NOT NULL default '', >> >`NameC` varchar( 255 ) default NULL , >> >`TypeC` varchar( 255 ) default NULL , >> >`Seat` varchar( 255 ) default NULL , >> >`Area_MI` decimal( 10, 1 ) default NULL , >> >`Area_KM` decimal( 10, 1 ) default NULL , >> >`Pop_2000` int( 10 ) default NULL , >> >`Pop_1990` int( 10 ) default NULL , >> >`Pop_MI` decimal( 10, 1 ) default NULL , >> >`Pop_KM` decimal( 10, 1 ) default NULL , >> >`Race1` int( 10 ) default NULL , >> >`Race2` int( 10 ) default NULL , >> >`Amerindian` int( 10 ) default NULL , >> >`White` int( 10 ) default NULL , >> >`Black` int( 10 ) default NULL , >> >`Asian` int( 10 ) default NULL , >> >`Pacific_Island` int( 6 ) default NULL , >> >`Some_Other_Race` int( 10 ) default NULL , >> >`Hispanic` int( 10 ) default NULL , >> >`id` int( 6 ) NOT NULL AUTO_INCREMENT , >> >PRIMARY KEY ( `id` ) , >> >KEY `SCode` ( `SCode` ) , >> >CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) >> REFERENCES >> >`statesarticles` ( `SCode` ) ON UPDATE CASCADE >> >) TYPE = InnoDB AUTO_INCREMENT =3143 >> > >> >MySQL said: >> >#1005 - Can't create table >> './[USERNAME]/counties.frm' >> >(errno: 150) >> > >> >Do you know what this means? >> >> You just got caught with the old Referential >> Integrity Gotcha. It >> will not allow you to create countries since >> statesarticles does not >> (yet) exist. >> >> The fix is to temporally turn off RI during the >> load. Put this >> command at the start of your file: >> >> SET FOREIGN_KEY_CHECKS = 0; >> >> and this one at the end: >> >> SET FOREIGN_KEY_CHECKS = 1; >> >> and it will work. > > OK, is this something I can do in phpMyAdmin or > another software program, or do I have to open the > MySQL file itself? I've scarcely touched MySQL files, > but I found the file counties.frm, which I assume I > would open with Notepad, right? > > And then I can make SET FOREIGN_KEY_CHECKS = 0; the > very first line and SET FOREIGN_KEY_CHECKS = 1; the > very last line, after which I would EXPORT my > database, then import it, right? > > And since I like to plan ahead, is it OK to insert > these two lines in ALL my MySQL documents, just to be > prepared for this error? If I inadvertently stick > these lines in a file that doesn't have a foreign key, > will it cause a problem? > > Finally, after I've imported my database online, do I > have to go back and remove these two lines, or can I > just leave them there indefinitely as a safeguard? >
You should put these lines to the dump file that contains CREATE TABLE statements, not to the *.frm files. Then load tables. If you use command-line client you can do: mysql> SET FOREIGN_KEY_CHECKS = 0; mysql> SOURCE file_name; mysql> SET FOREIGN_KEY_CHECKS = 1; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
