I have a table called person setup like this
(person_id SMALLINT UNSIGNED,
fname varchar(20),
lname varchar(20),
gender enum('M','F'),
birth_date date,
race enum('B','W','A','J'),
ethnicity varchar (60),
origin varchar(20),
profession varchar(40),
CONSTRAINT pk_person PRIMARY KEY (person_id));
and another called words setup like this
(person_id Smallint unsigned,
word_id smallint unsigned,
word Varchar(20),
format varchar(40),
title varchar(40),
CONSTRAINT pk_words PRIMARY KEY (word_id),
CONSTRAINT fk_words FOREIGN KEY (person_id)
REFERENCES person (person_id));
and i tried to import first like this
LOAD DATA LOCAL INFILE "samplepath"
INTO TABLE words
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r'
and got
error 1366 incorrect integer value: '""' for column 'word_id' at row 3
and
error 1452 cannot add or update a child row: a foreign key constraint fails ('words', CONSTRAINT 'fk_words" FOREIGN KEY ('person_id') REFERENCES 'person' ('person_id'))
but one value was inputted, and could be if i just added one at a time.
so then i tried
LOAD DATA LOCAL INFILE "samplepath"
INTO TABLE words
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r'
(person_id, @worder, word, format, title) set word_id = if(@worder = "", null, @worder);
but got the same errors in a different quantity.
Here's what my data looks like. Any idea whats wrong? I'm new to SQL
"person_id","word_id","word","format","title"
1,"","any","interview","breakfast club"
1,"","any","interview","breakfast club"
1,"","any","interview","breakfast club"
1,"","any","interview","breakfast club"
Also I tried using null, quotes, no quotes, all different types of formatting the data that you could imagine
person
with theperson_id
values you're loading? The second error you mention would seem to indicate that theperson_id
you're trying to insert doesn't exist in theperson
table.