MySQL Forums
Forum List  »  Newbie

Foreign Key constraint fails
Posted by: Meir Guttman
Date: April 14, 2021 01:58AM

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

Options: ReplyQuote


Subject
Written By
Posted
Foreign Key constraint fails
April 14, 2021 01:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.