How to solve this problem Cannot add or update a child row: a foreign key constraint fails?

Question

MySQL

Created a Java project that works with a database in MySql via JDBC. I have tables like this:

CREATE TABLE `brand` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin

CREATE TABLE `category` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `discount` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin

CREATE TABLE `producttype` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin

CREATE TABLE `product` (
  `id` int NOT NULL AUTO_INCREMENT,
  `brandId` int NOT NULL,
  `categoryId` int NOT NULL,
  `productTypeId` int NOT NULL,
  `price` double(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `brandId_idx` (`brandId`),
  KEY `categoryId_idx` (`categoryId`),
  KEY `prodoctTypeId_idx` (`productTypeId`),
  CONSTRAINT `brandId` FOREIGN KEY (`brandId`) REFERENCES `brand` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `categoryId` FOREIGN KEY (`categoryId`) REFERENCES `category` (`id`),
  CONSTRAINT `productTypeId` FOREIGN KEY (`productTypeId`) REFERENCES `producttype` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin

I created a Java console application to work with a database through Jdbc. Data was inserted into the Brand, Category and productType tables through Java without problems, but when I try to insert data into the Product table, I get the following error:

Cannot add or update a child row: a foreign key constraint fails (`internetshop`.`product`, CONSTRAINT `productTypeId` FOREIGN KEY (`productTypeId`) REFERENCES `producttype` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Answer 1

You are trying to write a row that has a value in the productTypeId field that is not in the productType table.

Answer 2

Before adding a product, you should add entries to the appropriate tables:

INSERT INTO `brand` (`name`) VALUES ('My own brand');

INSERT INTO `category` (`name`, `discount`) VALUES ('My sweet category', 8);

INSERT INTO `producttype` (`name`) VALUES ('My little productype');

INSERT INTO `product` (`brandId`,  `categoryId`, `productTypeId`, `price`) VALUES (2, 2, 2, 200.55);

SELECT *
FROM `product` `p`
JOIN `producttype` `pt` ON `pt`.`id` = `p`.`productTypeId` 
JOIN `category` `c` ON `c`.`id` = `p`.`categoryId` 
JOIN `brand` `b` ON `b`.`id` = `p`.`brandId` 
;

MySQL online test