2

I am inserting some data into the following MySQL tables:

CREATE TABLE genotype
(
Genotype VARCHAR(20),
Fitness FLOAT NULL,
Tally INT NULL,
PRIMARY KEY (Genotype)
)ENGINE=InnoDB;


CREATE TABLE gene
(
Gene VARCHAR(20),
E FLOAT NOT NULL,
Q2 FLOAT NOT NULL, 
PRIMARY KEY (Gene)
)ENGINE=InnoDB;

CREATE TABLE genotypegene
(
Genotype VARCHAR(20),
Gene VARCHAR(20),
FOREIGN KEY (Genotype) REFERENCES genotype(Genotype),
FOREIGN KEY (Gene) REFERENCES gene(Gene)
)ENGINE=InnoDB;

I inserted the data into genotype/gene first, but get the following error when trying to insert into genotypegene:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`populationdb`.`genotypegene`, CONSTRAINT `genotypegene_ibfk_2` FOREIGN KEY (`Gene`) REFERENCES `gene` (`Gene`))

The data I'm inserting is into this table is: Genotype1,Gene1 Genotype1,Gene2 Genotype1,Gene3 Genotype1,Gene4

There is one copy of Genotype1 in the genotype table, the idea being that each genotype can contain many genes, but each gene can exist in multiple genotypes (at the moment, there is only 1 genotype, but later I will insert more). I read here that I could turn off Foreign Key Checks, but I am reluctant to do so without knowing the reason for this error. Is this because there is only one copy of Genotype1 in the genotype table? (I have checked that Genotype1, Gene1 etc. are in the same format/spelling in their primary key tables).

Just in case, here is the code I am using to insert the data:

 mysql> LOAD DATA LOCAL INFILE 'C:\\.....genotypegene.csv'
   -> INTO TABLE genotypegene
   -> FIELDS TERMINATED BY ','
   -> (Genotype, Gene);

Thanks

Community
  • 1
  • 1
Lisa
  • 331
  • 1
  • 9
  • 15
  • 1
    Did you tried to add data with 'INSERT', not with 'LOAD DATA'? Maybe there is some spaces betwen fields, which are included when inserting via LOAD DATA. – rMX Dec 21 '11 at 10:39
  • 1
    the error tells us that there is missing parent data in the `Gene` table. Have you checked that all of the data with which you are trying to populate the `genotypegene.Gene` column exists in the `Gene` table? Also, have you checked for white space in either the CSV values or the `gene.Gene` column? Also the case of the values (upper, lower etc). Might be tripping you up too.. – Tom Mac Dec 21 '11 at 10:44
  • That works thanks - I didn't think of it, because the exact same data was inserting fine before I added Foreign Keys, but I guess that was because it didn't care that it had white space. – Lisa Dec 21 '11 at 10:51
  • How can I remove the whitespace from the CSV file (manually editting - there doesn't appear to be any). It works fine manually inserting, which is fine for the small amount of data I have at the moment, but eventually I will need to insert using LOAD DATA – Lisa Dec 21 '11 at 11:02
  • @Lisa If you don't have spaces in your values, you can easily replace in any text editor ' ' (whitespace) on '' (nothing) until there wouldn't be any witespaces. If there are wahitespaces in values, than better use RegExp, for example, you can use `sed`: `sed -e 's/ *, */,/g < infile > outfile'` – rMX Dec 21 '11 at 11:26

2 Answers2

9

One approach to find out what is causing this would be to do the following:

Disable Foreign Keys

SET FOREIGN_KEY_CHECKS = 0;

Load The Data

Do this using your command:

mysql> LOAD DATA LOCAL INFILE 'C:\\.....genotypegene.csv'
    -> INTO TABLE genotypegene
    -> FIELDS TERMINATED BY ','
    -> (Genotype, Gene);

Find Orphaned Data

select gtg.* from genotypegene gtg
where (gtg.Gene not in (select g.Gene from gene g) 
    or gtg.Genotype not in (select gt.Genotype from genotype gt));

This should give you a list of those rows that are causing your FK constraint violation.

Fix The Orphaned Data

Update them? Delete them? Fix them in the CSV? Insert parent row into Gene table? Do whatever is appropriate.

Enable FK Checks

SET FOREIGN_KEY_CHECKS = 1;

If nothing else this should give you a clue as to why your are getting the FK constraint violation error.

Good luck!

Tom Mac
  • 9,693
  • 3
  • 25
  • 35
  • Thanks for this, it turns out it is all my rows that are a problem, but this will be useful for the future – Lisa Dec 21 '11 at 15:29
  • +1 So many would have just given the solution with setting `FOREIGN_KEY_CHECKS = 0`, but getting the orphaned data is very nice to see mentioned. – blo0p3r May 01 '13 at 19:24
1

This error sometimes appears when the separator between fields coincides with some value that the field has. Example: If the separator is the comma (,). Possibly some field has this comma and believes it is a field change. Check these cases.

scopchanov
  • 7,966
  • 10
  • 40
  • 68
Manuel
  • 11
  • 1