Mysqlimport Error 1452
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the
Error 1452: Cannot Add Or Update A Child Row: A Foreign Key Constraint Fails
workings and policies of this site About Us Learn more about Stack error code 1452 mysql workbench Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions
Mysql Error 1452 Foreign Key Constraint Fails
Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join a foreign key constraint fails mysql insert them; it only takes a minute: Sign up mysqlimport: Error: 1452 up vote 0 down vote favorite I wrote a script in Perl that creates a database schema: CREATE TABLE descType ( id MEDIUMINT unsigned PRIMARY KEY, descr MEDIUMTEXT)ENGINE=InnoDB; CREATE TABLE taxType ( id MEDIUMINT unsigned PRIMARY KEY, descr TEXT not null)ENGINE=InnoDB; CREATE TABLE uniref( id INT unsigned PRIMARY cannot add or update a child row a foreign key constraint fails on delete cascade on update cascade) KEY, seqId varchar (50) not null, descId MEDIUMINT unsigned not null, n MEDIUMINT unsigned not null, taxId MEDIUMINT unsigned not null, repId varchar (50) not null, foreign KEY (descId) REFERENCES descType(id), FOREIGN KEY (taxId) REFERENCES taxType(id), unique(seqId) )ENGINE=InnoDB; When I use this command: system qq(mysqlimport -u$mySqlUser -p$mySqlPass $database $table --local --fields-terminated-by="\t" --lines-terminated-by="\r\n") )== 0 or die "ERROR: an error occurred while importing $table in $database. $?"; I get this error: mysqlimport: Error: 1452, Cannot add or update a child row: a foreign key constraint fails (`uniref_2013_08`.`uniref`, CONSTRAINT `uniref_ibfk_1` FOREIGN KEY (`descId`) REFERENCES `descType` (`id`)), when using table: uniref I can't figure out what I am doing wrong. I was using the same script on another machine and it was working fine. mysql perl foreign-keys mysqlimport share|improve this question edited Jan 1 '14 at 18:30 Eric Leschinski 47k23221190 asked Sep 13 '13 at 11:37 Angelo Ulivieri 133 Youre missing foreign key in desctype table. –Сухой27 Sep 13 '13 at 12:02 I read this: "Foreign key relationships involve a parent table that h
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us
Mysql Replication Error 1452
Learn more about Stack Overflow the company Business Learn more about hiring developers er_no_referenced_row_2 or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack
Set Foreign_key_checks=0 Mysql
Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up mysqlimport error message 1452 up vote 0 http://stackoverflow.com/questions/18785403/mysqlimport-error-1452 down vote favorite For this error message, is there a way for mysqlimport to output the specific row that fails the constraint? It's good for debugging. Otherwise, I don't know which row has this problem. mysqlimport: Error: 1452, Cannot add or update a child row: a foreign key constraint fails (`bwordDS`.`Category_Term`, CONSTRAINT `FK_qn1crq26sr0hqm5q2p7nfvdu1` FOREIGN KEY (`categories_id`) REFERENCES `Category` (`id`)), when using table: Category_Term mysql mysqlimport share|improve this http://stackoverflow.com/questions/36276053/mysqlimport-error-message-1452 question asked Mar 29 at 5:22 user697911 1,21421525 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote First check if Category table exist where you are trying to import this data. if Category table exists then need to check that all category_id in this table should exist in Category table as id. Better option is to import first category table and then this table. In generic first all parent tables data should import then child tables. A dirty way is as per below which is not recommended- set foreign_key_checks=0; import data here; set foreign_key_checks=1; Just to know which row is creating problem- Below query will provide you problematic rows. SELECT a.category_id FROM Category_Term a LEFT JOIN Category b ON a.category_id=b.id WHERE b.id IS NULL; Note: Assuming category_id in category_term and id in category tables will be indexed. share|improve this answer edited Mar 29 at 5:35 answered Mar 29 at 5:29 Zafar Malik 5,1901624 "check that all category_id in this table should exist in Category table as id.". I want to do this too. It's a large table. I have to write separate code to do this? It would be nice if mysqlimp
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings http://stackoverflow.com/questions/20028196/error-1452-mysql and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only error 1452 takes a minute: Sign up Error 1452 MySQL up vote 2 down vote favorite Inserting data into an empty table, but got error 1452. I am not sure why MySQL mentions the NameInfo table within the error. CREATE TABLE NameInfo ( Language VARCHAR(7) NOT NULL, Status VARCHAR(13) NOT NULL, Standard VARCHAR(13) NOT NULL, Name VARCHAR(13) NOT NULL, Name_ID INT(4) NOT foreign key constraint NULL, Place_ID INT(9) NOT NULL, Supplier_ID INT(4) NOT NULL, Date_Supplied DATE NOT NULL, PRIMARY KEY (Name_ID), FOREIGN KEY (Supplier_ID) REFERENCES Supplier(Supplier_ID), FOREIGN KEY (Place_ID) REFERENCES Place(Place_ID) ); CREATE TABLE Departments ( Dept_ID INT(6) NOT NULL, Dept_NAME VARCHAR(25) NOT NULL, DeptHead_ID INT(6) NOT NULL, DeptAA VARCHAR(20) NOT NULL, ParentDept_ID INT(4) NOT NULL, Location VARCHAR(10) NOT NULL, DeptType VARCHAR(12) NOT NULL, Primary key (Dept_ID) ); CREATE TABLE Employee ( Emp_ID INT(6) NOT NULL, Name VARCHAR(15) NOT NULL, Dept_ID INT(6) NOT NULL, Tax_ID INT(4) NOT NULL, Country VARCAR(15) NOT NULL, Hire_Date DATE NOT NULL, Birth_Date DATE NOT NULL, Salary INT(6) NOT NULL, Bonus INT(6) NOT NULL, AddressInfo VARCHAR(30) NOT NULL, PRIMARY KEY(Emp_ID), FOREIGN KEY(Dept_ID) REFERENCES Departments(Dept_ID) ); Inserted data to parent table, Departments, before child table, Employee. INSERT INTO Departments VALUES (040124,'Human Resource Division',405802,'Mohammed Siddiqui',1001,'California','HR'); INSERT INTO Employee VALUES (901126,'Kenneth Tran',040126,3013,'United States',06/01/2013,06/01/1992,80430,500,'N. 2nd St. Santa Clara, CA.'); ERROR 1452 (23000): Cannot add or update a child: a foreign key constraint fails ('namesinc'_'employee', CONSTRAINT 'employee-ibfk_1 'FOREIGN KEY ('Dept_ID') REFERENCES 'DEPARTMENTS' ('DEPT_ID')) Please let me know if I can provide additional information. mysql foreign-keys sh