From 2555a34b5568eb71d050a254819788d0cb152fb5 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 14 Dec 2018 12:49:44 -0300 Subject: [PATCH] Bug 21065: Set ON DELETE SET NULL on accountlines.borrowernumber Note: Why do we have ON UPDATE SET NULL on accountlines.itemnumber? --- installer/data/mysql/atomicupdate/bug_21848.perl | 16 ++++++++++++++++ installer/data/mysql/kohastructure.sql | 4 ++-- 2 files changed, 18 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_21848.perl diff --git a/installer/data/mysql/atomicupdate/bug_21848.perl b/installer/data/mysql/atomicupdate/bug_21848.perl new file mode 100644 index 0000000000..836cddf18a --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_21848.perl @@ -0,0 +1,16 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + $dbh->do(q| + ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_1; + |); + $dbh->do(q| + ALTER TABLE accountlines CHANGE COLUMN borrowernumber borrowernumber INT(11) DEFAULT NULL; + |); + $dbh->do(q| + ALTER TABLE accountlines ADD CONSTRAINT accountlines_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE; + |); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 21848 - Set ON DELETE SET NULL on accountlines.borrowernumber)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index c3c9570a29..9b45f15d15 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2705,7 +2705,7 @@ DROP TABLE IF EXISTS `accountlines`; CREATE TABLE `accountlines` ( `accountlines_id` int(11) NOT NULL AUTO_INCREMENT, `issue_id` int(11) NULL DEFAULT NULL, - `borrowernumber` int(11) NOT NULL default 0, + `borrowernumber` int(11) DEFAULT NULL, `accountno` smallint(6) NOT NULL default 0, `itemnumber` int(11) default NULL, `date` date default NULL, @@ -2722,7 +2722,7 @@ CREATE TABLE `accountlines` ( KEY `acctsborridx` (`borrowernumber`), KEY `timeidx` (`timestamp`), KEY `itemnumber` (`itemnumber`), - CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2.11.0