@@ -, +, @@ table for branchcodes - Update DB --- .../data/mysql/atomicupdate/bug_23066.perl | 17 +++++++++++++++++ installer/data/mysql/kohastructure.sql | 10 +++++----- 2 files changed, 22 insertions(+), 5 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_23066.perl --- a/installer/data/mysql/atomicupdate/bug_23066.perl +++ a/installer/data/mysql/atomicupdate/bug_23066.perl @@ -0,0 +1,17 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + # Add constraint for branchcode + unless ( foreign_key_exists( 'issues', 'issues_ibfk_3' ) ) { + $dbh->do(q{UPDATE issues i LEFT JOIN branches b ON (i.branchcode = b.branchcode) SET i.branchcode = NULL WHERE b.branchcode IS NULL}); + $dbh->do(q{ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE}); + } + unless ( foreign_key_exists( 'old_issues', 'old_issues_ibfk_3' ) ) { + $dbh->do(q{UPDATE old_issues i LEFT JOIN branches b ON (i.branchcode = b.branchcode) SET i.branchcode = NULL WHERE b.branchcode IS NULL}); + $dbh->do(q{ALTER TABLE old_issues ADD CONSTRAINT `old_issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE}); + } + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 23066 - Add foreign key for issues tables to branches table for branchcodes)\n"; +} --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -1746,7 +1746,8 @@ CREATE TABLE `issues` ( -- information related to check outs or issues KEY `branchcode_idx` (`branchcode`), KEY `bordate` (`borrowernumber`,`timestamp`), CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE, - CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE + CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE, + CONSTRAINT `issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- @@ -1776,10 +1777,9 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r KEY `old_issuesitemidx` (`itemnumber`), KEY `branchcode_idx` (`branchcode`), KEY `old_bordate` (`borrowernumber`,`timestamp`), - CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) - ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) - ON DELETE SET NULL ON UPDATE SET NULL + CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --