Foreign Key constraint fails
CREATE DATABASE `question`;
USE `question`;
CREATE TABLE `question`.`customers` (
`Customer` CHAR(30) NOT NULL,
`Contact` VARCHAR(25) DEFAULT NULL,
PRIMARY KEY (`Customer`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE=UTF8MB4_0900_AI_CI;
CREATE TABLE `question`.`deliverables` (
`Deliverable` CHAR(16) NOT NULL,
`Description` VARCHAR(1000) DEFAULT NULL,
PRIMARY KEY (`Deliverable`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE=UTF8MB4_0900_AI_CI;
CREATE TABLE `question`.`events` (
`Customer` CHAR(30) NOT NULL,
`Deliverable` CHAR(16) NOT NULL,
`Deliv_Date` DATE NOT NULL,
PRIMARY KEY (`Customer`,`Deliverable`,`Deliv_Date`),
KEY `FK_Event_Deliverable_idx` (`Deliverable`),
CONSTRAINT `FK_Event_Customer` FOREIGN KEY (`Customer`) REFERENCES `customers` (`customer`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `FK_Event_Deliverable` FOREIGN KEY (`Deliverable`) REFERENCES `deliverables` (`deliverable`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE=UTF8MB4_0900_AI_CI;
REPLACE INTO `question`.`customers` (Customer) VALUES ('Globes'),('Mishan'),('Maagan Michael'),('Meshulam');
REPLACE INTO `question`.`deliverables` (Deliverable) VALUES ('Baikal'),('Mustang'),('Western Desert'),('Wales');
REPLACE INTO `question`.`events` (Customer, Deliverable, Deliv_Date) VALUES
('Globes','Baikal','2020-02-07'),
('Globes','Mustang','2019-06-23'),
('Mishan','Western Desert','2017-05-03'),
('Maagan Michael','Wales','2018-08-29');
REPLACE INTO `question`.`customers` (Customer, Contact) VALUES ('Globes', 'Zohara Ron');
-- Response was:
-- Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails
-- (`question`.`events`, CONSTRAINT `FK_Event_Customer` FOREIGN KEY (`Customer`)
-- REFERENCES `customers` (`customer`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Why does the last REPLACE INTO query fail? The customer is an existing one! I can't see any FK violation...
Regards,
Meir