[Koha-bugs] [Bug 17160] New: Foreign key constraint for message_transports is too loose

bugzilla-daemon at bugs.koha-community.org bugzilla-daemon at bugs.koha-community.org
Mon Aug 22 16:23:03 CEST 2016


https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17160

            Bug ID: 17160
           Summary: Foreign key constraint for message_transports is too
                    loose
 Change sponsored?: ---
           Product: Koha
           Version: master
          Hardware: All
                OS: All
            Status: NEW
          Severity: enhancement
          Priority: P5 - low
         Component: Notices
          Assignee: koha-bugs at lists.koha-community.org
          Reporter: barton at bywatersolutions.com
        QA Contact: testopia at bugs.koha-community.org

I think this is the probable cause for Bug 13171.

http://git.koha-community.org/gitweb/?p=koha.git;a=blob;f=installer/data/mysql/kohastructure.sql;h=a05ef0684ae98f3fa1808fbc1533e43f684ebe9f;hb=HEAD#l2544

The message transports definition

--
-- Table structure for table `message_transports`
--

DROP TABLE IF EXISTS `message_transports`;
CREATE TABLE `message_transports` (
  `message_attribute_id` int(11) NOT NULL,
  `message_transport_type` varchar(20) NOT NULL,
  `is_digest` tinyint(1) NOT NULL default '0',
  `letter_module` varchar(20) NOT NULL default '',
  `letter_code` varchar(20) NOT NULL default '',
  `branchcode` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`message_attribute_id`,`message_transport_type`,`is_digest`),
  KEY `message_transport_type` (`message_transport_type`),
  KEY `letter_module` (`letter_module`,`letter_code`),
  CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`)
REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON
UPDATE CASCADE,
  CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`)
REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE
CASCADE ON UPDATE CASCADE,
  CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`,
`letter_code`, `branchcode`) REFERENCES `letter` (`module`, `code`,
`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Has

`message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON
UPDATE CASCADE,
  CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`,
`letter_code`, `branchcode`) REFERENCES `letter` (`module`, `code`,
`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE

The foreign key contstraints `module`, `code`, `branchcode` do *NOT* uniquely
identify a row in the letter table, because message_transport_type may vary.
Therefore, a deletion *any* message transport type will delete *all* rows
matching `module`, `code`, `branchcode`.

-- 
You are receiving this mail because:
You are the assignee for the bug.
You are watching all bug changes.


More information about the Koha-bugs mailing list