CODEWITHSUNDEEP
mysqlsqlforeign-keysinnodb
PMiller
PMiller

Reputation: 271

MySQL foreign key issue on junction table

I have five tables on my MySQL, all InnoDB.

Regular tables

Table list
Table proc
Table views

junction tables

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;

The idea is, that if a list record get's removed, all referenced l_proc and l_p_view records gets removed too. That does work as intended.

However, when I remove a record in l_proc with both a listId and procId as query, all records in l_p_view with the same listId are removed immediately - it doesn't matter that their procId is different than what submitted with the original removal query for l_proc.

What could I be missing here?

Rgds, P.

Upvotes: 1

Views: 482

Answers (1)

Tom Mac
Tom Mac

Reputation: 9853

I would try replacing this:

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;

with this:

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,

Difference is that you have a composite foreign key in the latter meaning that only those rows in l_p_view that match both the list_id and the proc_id of the deleted row in l_proc table will be deleted.

Upvotes: 2

Related Questions