连接表上的 MySQL 外键问题

Question

我的 MySQL 上有五个表，都是 InnoDB。

常规表

Table list
Table proc
Table views

连接表

Table l_p
Table l_p_views

CREATE TABLE IF NOT EXISTS `list` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `proc` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `view` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `l_proc` (
  `listId` int(10) unsigned NOT NULL,
  `procId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`listId`,`procId`),
  KEY `l_process_ibfk_2` (`procId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `l_proc`
  ADD CONSTRAINT `l_proc_ibfk_1` FOREIGN KEY (`listId`) REFERENCES `list` (`id`)     ON     DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `l_proc_ibfk_2` FOREIGN KEY (`procId`) REFERENCES `proc` (`id`) ON     DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS `l_p_view` (
  `listd` int(10) unsigned NOT NULL,
  `procId` int(10) unsigned NOT NULL,
  `viewId` int(10) unsigned NOT NULL,
  KEY `listId` (`listId`,`procId`,`viewId`),
  KEY `view` (`viewId`),
  KEY `l_p_view_ibfk_2_idx` (`procId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `l_p_view`
  ADD CONSTRAINT `l_p_view_ibfk_1` FOREIGN KEY (`listId`) REFERENCES `l_proc`     (`listId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `l_p_view_ibfk_3` FOREIGN KEY (`viewId`) REFERENCES `view` (`id`) ON     DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `l_p_view_ibfk_4` FOREIGN KEY (`procId`) REFERENCES `l_proc`     (`procId`) ON DELETE CASCADE ON UPDATE CASCADE;

这个想法是，如果列表记录被删除，所有引用的 l_proc 和 l_p_view 记录也会被删除。这确实按预期工作。

但是，当我删除 l_proc 中同时使用 listId 和 procId 作为查询的记录时，l_p_view 中具有相同 listId 的所有记录都会立即删除 - 它们的 procId 与原始删除时提交的记录不同并不重要查询 l_proc。

我可能会错过什么？

Rgds，

Answer 1

我会尝试替换这个:

ALTER TABLE `l_p_view`
  ADD CONSTRAINT `l_p_view_ibfk_1` FOREIGN KEY (`listId`) REFERENCES `l_proc`     (`listId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `l_p_view_ibfk_3` FOREIGN KEY (`viewId`) REFERENCES `view` (`id`) ON     DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `l_p_view_ibfk_4` FOREIGN KEY (`procId`) REFERENCES `l_proc`     (`procId`) ON DELETE CASCADE ON UPDATE CASCADE;

这样:

ALTER TABLE `l_p_view`
  ADD CONSTRAINT `l_p_view_ibfk_1` FOREIGN KEY (`listId`,`procId`) REFERENCES `l_proc`     (`listId`,`procId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `l_p_view_ibfk_3` FOREIGN KEY (`viewId`) REFERENCES `view` (`id`) ON     DELETE CASCADE ON UPDATE CASCADE,

区别在于，后者有一个复合外键，这意味着只有 l_p_view 中与 list_id 和 匹配的行l_proc 表中已删除行的 proc_id 将被删除。