--- "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?
Thanks.
__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
