Mysql cannot import from text file

Question

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

Answer 1

error 1366 incorrect integer value: '""' for column 'word_id' at row 3

This error is logical, basically, an empty string is not a number. That's the first problem.

Also I tried using null, quotes, no quotes, all different types of formatting the data that you could imagine

The problem with this is that if your word_id column is not nullable, you can't insert a null value into it. Also, formatting invalid data doesn't make it valid.

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'))

This error is normal. This is what Foreign Key constraints are for. Basically, what they do is make sure that you can't insert invalid data when referencing some other data

In this case, you reference a word (As far as I can tell), but you don't have words that have the ID value '' (empty string), so it doesn't allow you to insert the row with an incorrect reference.

Now that the errors are explained, there are two solutions:

1: Allow your word_id column to be null in your table schema. (This is possible even with foreign key constraints, however it might violate your database design). This way, you can insert a NULL value into the word_id column instead of an actual word_id.

2: Fix your data. Basically, you've built your database in such a way that you don't allow an empty or null value for the word_id column, which coincidentally means that your CSV data is invalid in the way that it is an empty string. You need to update the CSV data to have a correct reference to the word_id column for your data to be 'correct' again.

The so-called 'correct' approach according to how you've set up your database is the second one, where you fix the data. However, both solutions will work. Just make sure to document the changes to your database if you chose solution number 1 because the application which uses the data from your database might not be expecting NULL values and might break because of this.