CODEWITHSUNDEEP
mysqlforeign-keysalter-table
jerrygarciuh
jerrygarciuh

Reputation: 22018

Error #1452 when altering mySQL table to add foreign key

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?

Upvotes: 3

Views: 3328

Answers (2)

jerrygarciuh
jerrygarciuh

Reputation: 22018

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.

Upvotes: 0

CanSpice
CanSpice

Reputation: 35828

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.

Upvotes: 4

Related Questions