MySQL foreign key error

Question

I need to update rows in a child table,but I am getting following error while I run the code,

Company Table

CREATE TABLE `company` 
(
 `Company_id` int(11) NOT NULL AUTO_INCREMENT,
 `Company_name` varchar(255) DEFAULT NULL,
 `BuildingNameNumber` varchar(255) NOT NULL,
 `Street_name` varchar(255) NOT NULL,
 `Area` varchar(255) NOT NULL,
 `Landmark` varchar(255) NOT NULL,
 `PIN_code` int(6) NOT NULL,
 `PhoneMobile_number` char(15) NOT NULL,
 `Website` varchar(255) NOT NULL,
 `Fax` varchar(255) NOT NULL,
 `Email` varchar(255) NOT NULL,
 `Upload_logo` varchar(255) DEFAULT NULL,
 `tin_number` varchar(50) NOT NULL,
 `CreatedOn` datetime DEFAULT NULL,
 `CreatedBy` varchar(255) DEFAULT NULL,
 `UpdatedOn` datetime DEFAULT NULL,
 `UpdatedBy` varchar(255) DEFAULT NULL,
 `is_active` bit(1) DEFAULT b'0',
 PRIMARY KEY (`Company_id`),
);

Service Table

CREATE TABLE `service` (
`service_id` int(11) NOT NULL AUTO_INCREMENT,
 `company_id` int(11) NOT NULL,
 `serial_number` varchar(256) DEFAULT NULL,
 `service_name` varchar(256) DEFAULT NULL,
 `price` float(10,2) NOT NULL DEFAULT '0.00',
 `tax` float(5,2) NOT NULL DEFAULT '0.00',
 `is_active` bit(1) NOT NULL DEFAULT b'1',
 PRIMARY KEY (`service_id`),
 KEY `company_id` (`company_id`),
 CONSTRAINT `service_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`Company_id`) ON DELETE CASCADE ON UPDATE CASCADE
);

When I trying to update the service table I am getting error as

**Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`etailershop`.`service`, CONSTRAINT `service_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`Company_id`) ON DELETE CASCADE ON UPDATE CASCADE) 0.031 sec**

I was trying to find the error, I do understand the foreign key constraint but I could not find what is wrong with this, similar structure and constraint tables are working fine.

• Abdul Manaf: Please post your update query.

Answer 1

You're getting this error because you're trying to update a row to service table that does not have a valid value for the company_id field based on the values currently stored in company table.

If you post some more code someone can help you diagnose the specific cause.

• Rathish Kumar B: Yes, it seems like that only, but the company_id value is present in the company table, confusing..
• Rathish Kumar B: The update query is UPDATE service SET company_id = '183' AND service_name = 'Story Books' AND price ='400.00' WHERE service_id = '9' LIMIT 1.

Answer 2

I found the issue in my case, I used "AND" in my update query, I removed the "AND" operator, it is now working fine, the update statement and solved update statements are given below,

Update statement

UPDATE service SET company_id = '183' AND service_name = 'Story Books' AND price ='400.00' WHERE service_id = '9' LIMIT 1

Update Statement [SOLVED]

UPDATE service SET company_id = 187, service_name = 'Test Books', price = 500.00 WHERE service_id = 7

Small changes but working fine now.

• ypercubeᵀᴹ: You didn't just remove the AND, you replaced them with commas ,
• Rathish Kumar B: Yes, somehow, it is solved.