0

I am trying to update a field that is referenced as a foreign key in another table.

mysql> update Maintenance set contract='95096916-OLD' where contract='95096916';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (systems_doc.Equipment, CONSTRAINT Equipment_ibfk_1 FOREIGN KEY (contract) REFERENCES Maintenance (contract) ON UPDATE CASCADE)

| Maintenance | CREATE TABLE `Maintenance` (
  `contract` char(30) NOT NULL,
  `quote` char(30) NOT NULL,
  `vendor` char(20) NOT NULL,
  `provider` char(20) NOT NULL,
  `product` char(30) NOT NULL,
  `expiryDate` date NOT NULL,
  `annualCost` int(11) NOT NULL,
  `reference` char(13) NOT NULL,
  `purchaseOrder` char(13) NOT NULL,
  `cq` char(10) NOT NULL,
  `cqRenewal` char(10) NOT NULL,
  `comments` char(40) NOT NULL,
  UNIQUE KEY `contract` (`contract`,`expiryDate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 

| Equipment | CREATE TABLE `Equipment` (
  `vendor` char(20) NOT NULL,
  `model` char(30) NOT NULL,
  `serialNumber` char(20) NOT NULL,
  `purchaseOrder` char(20) NOT NULL,
  `purchaseDate` date NOT NULL,
  `contract` char(15) NOT NULL,
  `annualCost` int(11) NOT NULL,
  `comments1` varchar(256) DEFAULT NULL,
  `comments2` varchar(256) NOT NULL,
  PRIMARY KEY (`serialNumber`),
  KEY `contract` (`contract`),
  CONSTRAINT `Equipment_ibfk_1` FOREIGN KEY (`contract`) REFERENCES `Maintenance` (`contract`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Why isn't it cascading the UPDATE? I don't think it's circular. Thanks for your help. I've read other similar problems, but either I am misinterpreting how it should work, or I've got something setup wrong.

Uueerdo
  • 15,723
  • 1
  • 16
  • 21
CHB
  • 1
  • 2
    I'm surprised it lets a CHAR(15) reference a CHAR(30); was one of the columns perhaps changed after the FK was created? Have you tried changing their types to match? – Uueerdo Aug 08 '16 at 18:27
  • I think Uuuerdo has identified the problem. The datatypes of the `contract` columns don't match. If the datatype of the `contract` column in the child table (Equipment) was changed to be `CHAR(30)` (to match the referenced column) then we would expect the update to succeed. What's curious is that MySQL allowed the foreign key constraint to be created with the datatype mismatch. – spencer7593 Aug 08 '16 at 18:47
  • Brilliant!! That was it indeed. I am pretty positive I had the FK constraint in place before I changed that field. Thanks so much. – CHB Aug 09 '16 at 21:00

0 Answers0