MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `product` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `price` int(11) NOT NULL,
 `discount` int(11) NOT NULL,
 `unit_discount` varchar(255) NOT NULL,
 `sku` varchar(255) NOT NULL,
 `promocode` int(11) NOT NULL,
 `name` varchar(255) NOT NULL,
 `description` text NOT NULL,
 `photo_id` int(11) DEFAULT NULL,
 `weight` int(11) NOT NULL,
 `unit` varchar(255) NOT NULL,
 `property` json NOT NULL,
 `date_create` datetime NOT NULL,
 `restaurant_id` int(11) NOT NULL,
 `creator_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `key_sync_product_to_rest` (`restaurant_id`),
 KEY `key_sync_product_to_promo` (`promocode`) 
/* ,
 CONSTRAINT `key_sync_product_to_promo` FOREIGN KEY (`promocode`) REFERENCES `promocodes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `key_sync_product_to_rest` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
*/
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
CREATE TABLE `photos` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `product_id` int(11) DEFAULT NULL,
 `user_id` int(11) NOT NULL,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `key_sync_p_to_u` (`user_id`)
/* ,
 CONSTRAINT `key_sync_p_to_u` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
*/
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
ALTER TABLE `product`
  ADD CONSTRAINT `geidicsbbdfdb` FOREIGN KEY (`photo_id`) REFERENCES `photos` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE

Failed to add the foreign key constraint. Missing index for constraint 'geidicsbbdfdb' in the referenced table 'photos'


ALTER TABLE photos ADD INDEX (product_id);

ALTER TABLE `product`
  ADD CONSTRAINT `geidicsbbdfdb` FOREIGN KEY (`photo_id`) REFERENCES `photos` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE

Records: 0  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0


SHOW CREATE TABLE product;
SHOW CREATE TABLE photos;


Table
Create Table


product
CREATE TABLE `product` (
  `id` int NOT NULL AUTO_INCREMENT,
  `price` int NOT NULL,
  `discount` int NOT NULL,
  `unit_discount` varchar(255) NOT NULL,
  `sku` varchar(255) NOT NULL,
  `promocode` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `photo_id` int DEFAULT NULL,
  `weight` int NOT NULL,
  `unit` varchar(255) NOT NULL,
  `property` json NOT NULL,
  `date_create` datetime NOT NULL,
  `restaurant_id` int NOT NULL,
  `creator_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `key_sync_product_to_rest` (`restaurant_id`),
  KEY `key_sync_product_to_promo` (`promocode`),
  KEY `geidicsbbdfdb` (`photo_id`),
  CONSTRAINT `geidicsbbdfdb` FOREIGN KEY (`photo_id`) REFERENCES `photos` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3




Table
Create Table


photos
CREATE TABLE `photos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int DEFAULT NULL,
  `user_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `key_sync_p_to_u` (`user_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3