From 4c7181ed3bf8fd7647b7f7066e6cc8ef139e9c96 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Thu, 26 Sep 2019 14:36:06 +0100 Subject: [PATCH] Bug 23049: Add CHECK constraint to accountlines MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Séverine QUEUNE Signed-off-by: Marcel de Rooy Signed-off-by: Martin Renvoize --- installer/data/mysql/atomicupdate/bug_23049_debit.perl | 6 ++++++ installer/data/mysql/kohastructure.sql | 3 ++- 2 files changed, 8 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/atomicupdate/bug_23049_debit.perl b/installer/data/mysql/atomicupdate/bug_23049_debit.perl index f5fa7c2c97..e59fdd936d 100644 --- a/installer/data/mysql/atomicupdate/bug_23049_debit.perl +++ b/installer/data/mysql/atomicupdate/bug_23049_debit.perl @@ -89,6 +89,12 @@ if ( CheckVersion($DBversion) ) { } ); + $dbh->do( + qq{ + ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR debit_type IS NOT NULL) + } + ); + $dbh->do( qq{ UPDATE accountlines SET debit_type = accounttype, accounttype = NULL WHERE accounttype IN (SELECT code from account_debit_types) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 87d219ab29..130949ea2a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2683,7 +2683,8 @@ CREATE TABLE `accountlines` ( CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, - CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type`) REFERENCES `account_debit_types` (`code`) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type`) REFERENCES `account_debit_types` (`code`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `accountlines_check_type` CHECK (`accounttype` IS NOT NULL OR `debit_type_code` IS NOT NULL) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- 2.39.2