John,
please print a detailed description of the latest foreign key error with
SHOW INNODB STATUS
and post it here.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php
Order MySQL Network from http://www.mysql.com/network/
Thank you! I won't bother to post the results because the command showed me the problem. The problem appears to be that the parser doesn't skip whitespace after separators as I assumed it did. The result is that my key was "Administrator", but it was looking for " Administrator". Removing all whitespace allowed it to work correctly.
To me, this parsing is either a bug or a "feature" that should be documented in red letters. But it isn't a InnoDB issue.
Thanks again for your help by pointing out this very useful command.
----- Original Message ----- From: "John Swartzentruber" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Sunday, February 20, 2005 11:54 PM
Subject: Problems with LOAD DATA INFILE
I am new to SQL and to MySQL, but am working with it on a project for a graduate Database system course. I'm running MySQL 4.1.10 under Windows 2000. I am trying to load a table from a text file. The table in question has a foreign key. The table that it references contains data.
The problem is that when I load the data using LOAD DATA LOCAL INFILE, I get this error:
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
That seems clear enough except that the foreign key constraint should *not* fail. I've verified that the value exists in the other table.
What is really strange is that when I tried inserting the data directly using "INSERT INTO", it works. In this particular case, I only need to load a handful of records, so using INSERT INTO is an option, but I would really like to figure out what isn't working.
Here is my table definition:
create table Subscriber ( UserID int auto_increment, Name varchar(50) not null, Password varchar(8) not null, EmailAddress varchar(50), SGroupName varchar(50), primary key (UserId), foreign key (SGroupName) references AccessGroup(GroupName) on update cascade ) ENGINE=INNODB;
Here is how I am attempting to load it:
LOAD DATA LOCAL INFILE 'c:/Documents and Settings/john/My Documents/Grad School/Project/LoadData/Subscriber.csv' REPLACE INTO TABLE Subscriber FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (Name, Password, EmailAddress, SGroupName);
Here are the first two lines of my data file:
Name, Password, EmailAddress, SGroupName "John Swartzentruber", "8490JTTT", "[EMAIL PROTECTED]", Administrator
And here is what *does* work:
insert into Subscriber (Name, Password, EmailAddress, SGroupName) values ("John Swartzentruber", "8490JTTT", "[EMAIL PROTECTED]", "Administrator");
Can anyone see what my problem is? I really appreciate any assistance
you can provide. I hope this is the appropriate group for newbie questions.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
