Error #1452 when altering mySQL table to add foreign key

Question

Trying to help an intern with her project. She wants to add foreign keys to an existing table but this query:

ALTER TABLE `document` 
  ADD CONSTRAINT `document_ibfk_1` FOREIGN KEY (`cle_author`) 
  REFERENCES `author` (`id_author`) 
  ON DELETE CASCADE 
  ON UPDATE CASCADE;

gives this error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`wrc_mysql`.<result 2 when explaining filename '#sql-30e4_7000d'>, CONSTRAINT `document_ibfk_1` FOREIGN KEY (`cle_author`) REFERENCES `author` (`id_author`) ON DELETE CASCADE ON UPDATE CASCADE)

Schema are like so

CREATE TABLE `document` (
  `id_document` int(11) NOT NULL AUTO_INCREMENT,
  `abstract` text,
  `number_of_pages` int(10) DEFAULT NULL,
  `original_surrey_citation` varchar(255) DEFAULT NULL,
  `updated_citation` varchar(255) DEFAULT NULL,
  `library_of_congress` varchar(10) DEFAULT NULL,
  `cross_citation` varchar(50) DEFAULT NULL,
  `doc_type` varchar(255) DEFAULT NULL,
  `questions` varchar(255) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `cle_author` int(10) NOT NULL,
  PRIMARY KEY (`id_document`),
  KEY `cle_author` (`cle_author`)
) ENGINE=InnoDB AUTO_INCREMENT=22591 DEFAULT CHARSET=utf8

CREATE TABLE `author` (
  `id_author` int(10) NOT NULL AUTO_INCREMENT,
  `author_name` varchar(255) DEFAULT NULL,
  `sender_office` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_author`),
  KEY `author_name` (`author_name`,`sender_office`)
) ENGINE=InnoDB AUTO_INCREMENT=22591 DEFAULT CHARSET=utf8

Anyone know what is going wrong?

Does your data in the `document` table agree with that in the `author` table? Does every `cle_author` value in the `document` table have a corresponding entry in the `author` table? — CanSpice, Mar 31 '11 at 22:00
possible duplicate of [Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails](http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fai) — CanSpice, Mar 31 '11 at 22:00
Hey, you are correct. This query shows bad references: SELECT cle_author FROM document doc LEFT JOIN author a ON doc.cle_author=a.id_author WHERE a.id_author IS NULL; — jerrygarciuh, Mar 31 '11 at 22:09

score 4 · Accepted Answer · answered Mar 31 '11 at 22:11

4

You probably have inconsistent data between your two tables. This error means that you have a cle_author value in your document table that doesn't have a corresponding entry in the author table. Since the cle_author value is going to be set up as a foreign key, each value for that field must have a corresponding entry in the author table's id_author field.

answered Mar 31 '11 at 22:11

CanSpice

34,814
10
72
86

if the data in the tables is not important : TRY THIS -> delete all the records in your tables and then try add the foreign key – Kugan Kumar Jul 04 '17 at 08:02

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

Per this page: Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails

checked intern's data with

SELECT cle_author FROM document doc LEFT JOIN author a ON doc.cle_author=a.id_author WHERE a.id_author IS NULL;

And found ALL of her cle_author data is bogus and does not hold valid references to id_author values.

edited May 23 '17 at 12:23

Community

1
1

answered Mar 31 '11 at 22:12

jerrygarciuh

21,158
26
82
139

Error #1452 when altering mySQL table to add foreign key

2 Answers2