error 1452 "cannot add or update a child row: foreign key constaint fails"...

[Jonathan]

Hey guys,

Taking a DB class right now, and having a pain dealing with foreign key problems.

Every time I try to load a txt file of data using the "load data infile" command, I am getting the following error:
error 1452 "cannot add or update a child row: foreign key constaint fails"

Now, I know this is error is not directly related to the load data infile command. But rather, it is somehow related to the foreign key constaints; but I can't see it for the life of me.

The text file is loading data to a customer table (schema shown below), and this customer table has a foreign key sales_emp_ID, which represents the ID of the salesperson that is assigned to the particular customer. Now, here's one fact: for ALL tuples in the customer txt file, the foreign key used IS already in the emp_salaried table. So no one can say, "well the problem is that you are loading data with a foreign key and that key is not in the parent table." This is not the case.

And here is what is really eating at me. If I take the customers.txt file and delete all rows except one, and it doesn't matter which one I leave, the load WORKS. NO ERRORS. But as soon as I add a row, ANY OTHER ROW, and I've tried this with different rows and different sets of rows. As soon as I add a 2nd row of data, BOOM, the error shows its nasty face. It's so weird!

So talk to me. What the heck is this? Hopefully you guys can be patient with me and my ignorance of mySQL. Below are clippings of the script files I am using to create and populate my database.

Here's a paste of part of the create.sql script file I have:
----------------------------------------------------------
#DROP any previous database of same name
drop database if exists pp;

#Create and USE this new pp database
CREATE DATABASE pp;
USE pp;

#Create Employee table consisting of basic employee information
CREATE TABLE employee (
emp_ID VARCHAR(10) NOT NULL,
fname VARCHAR(20),
mname VARCHAR(20),
lname VARCHAR(20),
phone1 VARCHAR(20),
phone2 VARCHAR(20),
email VARCHAR(50),
birthdate DATE,
date_hired DATE,
address1 VARCHAR(40),
address2 VARCHAR(40),
city VARCHAR(20),
state VARCHAR(2),
zip VARCHAR(10),
emp_type CHAR(1),
PRIMARY KEY (emp_ID)
) ENGINE=INNODB;

#Each Employee is either salaried or a salesperson. The following two tables
#contain the necessary information for each.
#Salaried employees have a salary and vacation time that salespeople do not have.
#The emp_ID is a foreign key in this table, and comes from the main employee table
CREATE TABLE emp_salaried (
salaried_emp_ID VARCHAR(10) NOT NULL,
salary double,
vacation_time double,
PRIMARY KEY (salaried_emp_ID),
index (salaried_emp_ID),
FOREIGN KEY (salaried_emp_ID) REFERENCES employee (emp_ID)
ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB;

#Salespeople simply need to additionally record their respective commission rates.
#Again, the emp_ID is a foreign key, and comes from the main employee table.
CREATE TABLE emp_sales (
sales_emp_ID VARCHAR(10) NOT NULL,
commission_rate DECIMAL,
PRIMARY KEY (sales_emp_ID),
index (sales_emp_ID),
FOREIGN KEY (sales_emp_ID) REFERENCES employee (emp_ID)
ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB;

#Create customer table. Each customer is assigned to a sales rep.
#Therefore, emp_ID is a foreign key in this table, and refers to the emp_sales table.
CREATE TABLE customers (
cust_num VARCHAR(10) NOT NULL,
name VARCHAR(60),
contact VARCHAR(40),
phone1 VARCHAR(20),
phone2 VARCHAR(20),
fax VARCHAR(20),
email VARCHAR(60),
website VARCHAR(60),
address1 VARCHAR(600),
address2 VARCHAR(60),
city VARCHAR(30),
state VARCHAR(2),
zip VARCHAR(10),
country VARCHAR(20),
sales_emp_ID VARCHAR(10) NOT NULL,
PRIMARY KEY (cust_num),
index (sales_emp_ID),
FOREIGN KEY (sales_emp_ID) REFERENCES emp_sales (sales_emp_ID)
ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB;
----------------------------------------------------------


The problem is coming when I add data to the customer table, and if you notice, sales_emp_ID is a foreign key of the customer table, because each customer has an assigned salesperson.

Here's a pasting of part of the populate.sql script file to insert data into the aforementioned tables:


----------------------------------------------------------
#Command to use appropriate database
use pp;

#Insert employees into Employee table
INSERT INTO employee VALUES ('JD-01', 'John', 'Michael', 'Doe', '407-678-5555', NULL, '[e-mail]', '1970-01-01', '2001-01-01', 'UCF Boulevard', 'Box jmd', 'Orlando', 'FL', '32828', 'S');
INSERT INTO employee VALUES ('PS-01', 'Peter', 'David', 'Smith', '407-508-5555', '407-828-8764', '[e-mail]', '1960-01-01', '2001-01-01', 'UCF Boulevard', 'Box pds', 'Orlando', 'FL', '32828', 'S');
INSERT INTO employee VALUES ('SR-01', 'Susanna', 'Adrian', 'Richards', '407-368-5555', NULL, '[e-mail]', '1980-01-01', '2003-08-01', 'UCF Boulevard', 'Box sar', 'Orlando', 'FL', '32828', 'S');
INSERT INTO employee VALUES ('NC-01', 'Nancy', 'Caroline', 'Clopp', '407-132-5555', NULL, '[e-mail]', '1940-01-01', '2001-01-01', 'UCF Boulevard', 'Box ncc', 'Orlando', 'FL', '32828', 'R');
INSERT INTO employee VALUES ('SK-01', 'Sahar', 'Sensation', 'Krishntuff', '407-875-5555', NULL, '[e-mail]', '1978-01-01', '2001-01-01', 'UCF Boulevard', 'Box ssk', 'Orlando', 'FL', '32828', 'R');

#Insert the information of salaried employees into emp_salaried table
INSERT INTO emp_salaried VALUES ('NC-01', 28000, 0);
INSERT INTO emp_salaried VALUES ('SK-01', 98000, 40);

#Insert the information of sales reps into emp_sales table
INSERT INTO emp_sales VALUES ('JD-01', 50);
INSERT INTO emp_sales VALUES ('PS-01', 35);
INSERT INTO emp_sales VALUES ('SR-01', 40);

#The following four load data file commands load tab delimited text files into the appropriate tables as shown:
load data infile './customers.txt' into table customers;
----------------------------------------------------------
And note, that the customers.txt file is located in the ROOT of the data directory.

And lastly, here's a clipping from the customers.txt file that is being loaded (tab delimited, and hopefully, if any of you are willing to test this, hopefully all these things will paste okay with their appropriate tabs, etc):
----------------------------------------------------------
TEST-01 COMPA namea [phone] \N [phone] [e-mail] \N "803 West Broad Street, Suite 600" \N Falls Church VA 22046 \N SR-01
TEST2-01 COMPB nameb [phone] \N \N [e-mail] \N 4132 Peters Road \N Harvey LA 70058 \N PS-01
TEST3-01 COMPC namec [phone] \N [phone] [e-mail] \N 4124 Little Road \N Trinity FL 34655 \N PS-01
TEST4-01 COMPD named [phone] \N \N [e-mail] \N P O Box 5000 138 Highway 3218 LaPlace La 70068 \N JD-01
----------------------------------------------------------


PLEASE HELP!

Thanks.