mysql - 外键关系在 mysql 中不起作用

Question

这里我正在为我的 SQL 做外键关系，我有 3 个表

1. 产品
2. 画廊
3. 报价

这里product表是主表，gallery、offer表是子表，现在我尝试删除主表条目，但是它没有发生，我收到类似

的错误

Cannot delete or update a parent row: a foreign key constraint fails

    CREATE TABLE `product` (
 `productId` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) COLLATE utf16_bin NOT NULL,
 `price` decimal(10,0) NOT NULL,
 `regOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf16 COLLATE=utf16_bin

CREATE TABLE `offer` (
 `offerId` int(11) NOT NULL AUTO_INCREMENT,
 `productId` int(11) NOT NULL,
 `offerpercentage` text COLLATE utf16_bin NOT NULL,
 PRIMARY KEY (`offerId`),
 KEY `productId` (`productId`),
 CONSTRAINT `offer_ibfk_1` FOREIGN KEY (`productId`) REFERENCES `product` (`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf16 COLLATE=utf16_bin

CREATE TABLE `gallery` (
 `galleryId` int(11) NOT NULL AUTO_INCREMENT,
 `productId` int(11) NOT NULL,
 `galleryName` text COLLATE utf16_bin NOT NULL,
 PRIMARY KEY (`galleryId`),
 KEY `productId` (`productId`),
 CONSTRAINT `gallery_ibfk_1` FOREIGN KEY (`productId`) REFERENCES `product` (`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf16 COLLATE=utf16_bin

Expected output

Suppose I am deleting master table entry means i have to delete all child tables entry also

Answer 1

您需要将ON DELETE CASCADE应用于您的外键引用。

CREATE TABLE `product` (
 `productId` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) COLLATE utf16_bin NOT NULL,
 `price` decimal(10,0) NOT NULL,
 `regOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf16 COLLATE=utf16_bin

CREATE TABLE `offer` (
 `offerId` int(11) NOT NULL AUTO_INCREMENT,
 `productId` int(11) NOT NULL,
 `offerpercentage` text COLLATE utf16_bin NOT NULL,
 PRIMARY KEY (`offerId`),
 KEY `productId` (`productId`),
 CONSTRAINT `offer_ibfk_1` FOREIGN KEY (`productId`) REFERENCES `product` (`productId`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf16 COLLATE=utf16_bin

CREATE TABLE `gallery` (
 `galleryId` int(11) NOT NULL AUTO_INCREMENT,
 `productId` int(11) NOT NULL,
 `galleryName` text COLLATE utf16_bin NOT NULL,
 PRIMARY KEY (`galleryId`),
 KEY `productId` (`productId`),
 CONSTRAINT `gallery_ibfk_1` FOREIGN KEY (`productId`) REFERENCES `product` (`productId`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf16 COLLATE=utf16_bin

这告诉MySQL，当删除父级时，删除引用的子级。