CODEWITHSUNDEEP
mysqlinnodb
GettingStarted
GettingStarted

Reputation: 7625

Can't delete records from table

CREATE TABLE `recipe` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ethnicityID` int(25) NOT NULL,
  `recipename` varchar(225) COLLATE utf8_unicode_ci NOT NULL,
  `recipedescription` text COLLATE utf8_unicode_ci NOT NULL,
  `recipeprocedure` text COLLATE utf8_unicode_ci NOT NULL,
  `recipepremium` enum('YES','NO') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'NO',
  `recipeusercreator` enum('YES','NO') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'NO',
  `recipecreatorname` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `recipeapprovedby` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `recipeapproveddate` timestamp NULL DEFAULT NULL,
  `recipestatus` enum('YES','NO') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'NO',
  `reciperequiresAdultSupervision` enum('YES','NO') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'NO',
  `category_id` int(12) DEFAULT NULL,
  `facebookID` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `recipetype` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `language` varchar(225) COLLATE utf8_unicode_ci NOT NULL,
  `app` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `added_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `fbID` (`facebookID`),
  KEY `foreignkey_PerCategory` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

This is my Table Syntax for a MySQL Table.

When I attempt to run this query 

DELETE FROM `recipe` WHERE `ID` IN ('12','13','14','15','16','17','18');

I get the following error

Cannot delete or update a parent row: a foreign key constraint fails (`desifoodapp`.`recipe_image`, CONSTRAINT `recipe_image_ibfk_1` FOREIGN KEY (`recipe_id`) REFERENCES `recipe` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE)

I want to know how I can correct my table so that if a recipe is deleted, it's images are also deleted from the recipe_image table but the category that the recipe belongs to is not deleted. It's not affected.

recipe_image table below

CREATE TABLE `recipe_image` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `recipeimage` varchar(225) DEFAULT NULL,
  `recipe_id` int(12) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `recipe_id` (`recipe_id`),
  CONSTRAINT `recipe_image_ibfk_1` FOREIGN KEY (`recipe_id`) REFERENCES `recipe` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;

category table below

CREATE TABLE `category` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `categoryname` varchar(225) NOT NULL,
  `categoryimage` varchar(225) NOT NULL,
  `facebookID` int(25) NOT NULL,
  `added_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

Upvotes: 0

Views: 4133

Answers (3)

Haji
Haji

Reputation: 2077

You can't make delete operation when any field in the table you are deleting has referred in another table as foreign key and the referenced table has any entry.

Instead you can use cascade delete operation to delete all records in your table and fields referenced tables. or you can use rule on the table you are going to delete

Refer the following link for cascade delete operation : Cascade Delete

Upvotes: 0

Kirk
Kirk

Reputation: 5077

Just make it CASCADE for Deletion as well and try

lets assume you have recipe table as Master, other tables are just liked on. when you make a foreign key constrain, just set them as CASCADE Delete and CASCADE update so whenever you make changes in the master table, that is going to update all other tables as well. or if you delete one record in master table and will check all foreign key links and delete them on all client records as well

SET these lines in other tables definition

ON DELETE CASCADE ON UPDATE CASCADE

for instance : recipre_image

CONSTRAINT `recipe_image_ibfk_1` FOREIGN KEY (`recipe_id`) REFERENCES `recipe` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE

Upvotes: 2

ddoor
ddoor

Reputation: 5973

The foreign key constrain stops you from deleting things out of the table. You have to do a cascading delete or something similar:

SQL Server: Deleting Rows with Foreign Key Constraints: Can Transactions override the constraints?

Upvotes: 0

Related Questions