@@ -, +, @@ messages.borrowernumber --- installer/data/mysql/atomicupdate/bug_13515.perl | 20 ++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 3 ++- 2 files changed, 22 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/bug_13515.perl --- a/installer/data/mysql/atomicupdate/bug_13515.perl +++ a/installer/data/mysql/atomicupdate/bug_13515.perl @@ -0,0 +1,20 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + unless( foreign_key_exists( 'messages', 'borrowernumber' ) ) { + $dbh->do(q| + DELETE m FROM messages m + LEFT JOIN borrowers b ON m.borrowernumber=b.borrowernumber + WHERE b.borrowernumber IS NULL + |); + + $dbh->do(q| + ALTER TABLE messages + ADD CONSTRAINT messages_borrowernumber + FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE + |); + } + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 13515 - Add a FOREIGN KEY constaint on messages.borrowernumber)\n"; +} --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -2694,7 +2694,8 @@ CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- the date and time the message was written `manager_id` int(11) default NULL, -- creator of message PRIMARY KEY (`message_id`), - CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL + CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL, + CONSTRAINT `messages_borrowernumber` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --