CODEWITHSUNDEEP
mysqlforeign-keysmysql-workbenchmysql-error-1452
Yeshlol
Yeshlol

Reputation: 11

Error 1452 MySQL Load Infile (but correct data)

I'm trying to load data from a .txt using LOAD DATA INFILE, the problem is that i get error 1452, but the foreign keys referred are present in my DB. Also other LOAD DATA are working, just can't solve this and 1 other load.

I've checked the referred data, and are present in DB before i load. Also the columns are of the same type. I tried reinstalling MySQL still not working (but a friend of mine using the same code/.txt can load the data). I can load the .txt if it consists of a single line, but when adding a second one i got the error.

-- The table referred:
CREATE TABLE Categoria (
Nome    VARCHAR(50)     NOT NULL,
Immagine    MEDIUMBLOB,
PRIMARY KEY (Nome));

-- The table with FK:
CREATE TABLE Sottocategoria_Di (
Categoria1  VARCHAR(50)     NOT NULL,
Categoria2  VARCHAR(50)     NOT NULL,
PRIMARY KEY (Categoria1, Categoria2),
FOREIGN KEY (Categoria1) REFERENCES Categoria(Nome) ON DELETE NO ACTION,
FOREIGN KEY (Categoria2) REFERENCES Categoria(Nome) ON DELETE CASCADE);

INSERT INTO Categoria VALUES ('Chitarra', NULL);
INSERT INTO Categoria VALUES ('Chitarra Acustica', NULL);
INSERT INTO Categoria VALUES ('Chitarra Classica', NULL);

LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 
8.0/Uploads/MusicShop/Sottocategoria.txt" INTO TABLE 
Music.Sottocategoria_Di
CHARACTER SET latin1
FIELDS TERMINATED BY '|' 
ENCLOSED BY '' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(Categoria1,Categoria2);

-- Sottocategoria.txt
Categoria1,Categoria2
Chitarra Classica|Chitarra
Chitarra Acustica|Chitarra

A friend of mine reinstalled MySQL and using the same exact script/.txt can load the file but i still can't.

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (music.sottocategoria_di, CONSTRAINT sottocategoria_di_ibfk_2 FOREIGN KEY (Categoria2) REFERENCES categoria (Nome) ON DELETE CASCADE)

Upvotes: 1

Views: 426

Answers (1)

Yeshlol
Yeshlol

Reputation: 11

The problem was using lines terminated by '\n', switched to '\r\n' and it worked! Still don't know why it works for other files .txt i'm using (same OS Windows)

Upvotes: 0

Related Questions