prati prati - 6 months ago 15
SQL Question

How to resolve MySQL error "Cannot delete or update a parent row: a foreign key constraint fails"?


Cannot delete or update a parent row: a foreign key constraint fails (`sponge`.`taxonomy`, CONSTRAINT `taxonomy_ibfk_1` FOREIGN KEY (`organism_id`) REFERENCES `organism` (`organism_id`))


I am getting this error while deleting entire record.

Actually I wanted to deleted entire record from all table associated by Organism_id in sponge DB.

My Organism Table is:-

CREATE TABLE IF NOT EXISTS `organism` (
`organism_id` int(11) NOT NULL AUTO_INCREMENT,
`experts_id` int(11) NOT NULL,
`literature_id` int(11) NOT NULL,
`genus` varchar(255) NOT NULL,
`species` varchar(255) NOT NULL,
`scientific_name` varchar(255) NOT NULL,
`organism_type` varchar(255) NOT NULL,
`author_org` varchar(255) NOT NULL,
`found_year` varchar(255) NOT NULL,
`curated_year` date NOT NULL,
`curated_status` varchar(10) NOT NULL,
PRIMARY KEY (`organism_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=60 ;


and Taxonomy table is:

CREATE TABLE IF NOT EXISTS `taxonomy` (
`taxonomy_id` int(11) NOT NULL AUTO_INCREMENT,
`organism_id` int(11) NOT NULL,
`kingdom` varchar(255) NOT NULL,
`phylum` varchar(255) NOT NULL,
`class` varchar(255) NOT NULL,
`order_tax` varchar(255) NOT NULL,
`family` varchar(255) NOT NULL,
PRIMARY KEY (`taxonomy_id`),
KEY `organism_id` (`organism_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=50 ;


but when i am using delete query:-

SELECT a.*,u.*,s.*,b.*,c.*,d.*,e.*,i.*,m.*
FROM organism a
JOIN taxonomy u
ON u.organism_id = a.organism_id
JOIN synonym s
ON s.organism_id = u.organism_id_id
JOIN biogeography b
ON b.organism_id = s.organism_id_id
JOIN common_name c
ON c.organism_id = b.organism_id_id
JOIN description d
ON d.organism_id = c.organism_id_id
JOIN ecology e
ON e.organism_id = d.organism_id_id
JOIN economic_importance i
ON i.organism_id = e.organism_id_id
JOIN images m

ON m.organism_id = i.organism_id_id

WHERE m.organism_id = 59


I am getting above error...

How can I change my delete query to remove above #1452 error?

Answer

As Bob0t said in the comments, the error is caused by the presence of values linked to your organism_id, which by default prevents the deletion of the parent row.

You should change the definition of your taxonomy table and add the following:

CREATE TABLE IF NOT EXISTS `taxonomy` (
`taxonomy_id` int(11) NOT NULL AUTO_INCREMENT,
`organism_id` int(11) NOT NULL,
`kingdom` varchar(255) NOT NULL,
`phylum` varchar(255) NOT NULL,
`class` varchar(255) NOT NULL,
`order_tax` varchar(255) NOT NULL,
`family` varchar(255) NOT NULL,
PRIMARY KEY (`taxonomy_id`),
CONSTRAINT org_id FOREIGN KEY (`organism_id`) REFERENCES organism(`organism_id`) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=50 ;

This way, when you delete the parent row (in table organism) you also delete all its referencing children in table taxonomy.

Edit

For completeness of the answer, the delete query should be something as simple as

DELETE FROM `organism` WHERE `organism_id` = 59;

where 59 comes from your question WHERE clause, but it can be any valid ID.

Comments