Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Please post your update query. –  Abdul Manaf Feb 26 at 10:53

2 Answers 2

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.

share|improve this answer
    
Yes, it seems like that only, but the company_id value is present in the company table, confusing.. –  rathishDBA Feb 27 at 13:59
    
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. –  rathishDBA Feb 27 at 14:12

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.

share|improve this answer
1  
You didn't just remove the AND, you replaced them with commas , –  ypercube Feb 27 at 14:33
    
Yes, somehow, it is solved. –  rathishDBA Feb 27 at 14:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.