From 9b018fc6de35a51a1eaeae6868db050630ccc484 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Tue, 23 Aug 2022 13:00:17 +0000 Subject: [PATCH] Bug 30490: Adjust constraint itemtypes.itemtypes_ibfk_1 Change from CASCADE to restrict. In harmony with dbrev 20.06.00.022. Test plan: Run the dbrev. Bonus: update itemtypes set parent_type='VM' where itemtype='CF'; delete from itemtypes where itemtype='VM'; => ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`koha_myclone`.`itemtypes`, CONSTRAINT `itemtypes_ibfk_1` FOREIGN KEY (`parent_type`) REFERENCES `itemtypes` (`itemtype`)) Signed-off-by: Marcel de Rooy Signed-off-by: Nick Clemens Signed-off-by: Tomas Cohen Arazi --- installer/data/mysql/atomicupdate/bug_30490.pl | 18 ++++++++++++++++++ installer/data/mysql/kohastructure.sql | 2 +- 2 files changed, 19 insertions(+), 1 deletion(-) create mode 100755 installer/data/mysql/atomicupdate/bug_30490.pl diff --git a/installer/data/mysql/atomicupdate/bug_30490.pl b/installer/data/mysql/atomicupdate/bug_30490.pl new file mode 100755 index 0000000000..9b5afad3a6 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_30490.pl @@ -0,0 +1,18 @@ +use Modern::Perl; + +return { + bug_number => 30490, + description => "Adjust FK constraint for parent item type", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + if( foreign_key_exists('itemtypes', 'itemtypes_ibfk_1') ) { + $dbh->do(q{ +alter table itemtypes drop foreign key itemtypes_ibfk_1; + }); + } + $dbh->do(q{ +alter table itemtypes add foreign key itemtypes_ibfk_1 (`parent_type`) REFERENCES `itemtypes` (`itemtype`); + }); + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 15821407e9..6ee27bb9ee 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3344,7 +3344,7 @@ CREATE TABLE `itemtypes` ( PRIMARY KEY (`itemtype`), UNIQUE KEY `itemtype` (`itemtype`), KEY `itemtypes_ibfk_1` (`parent_type`), - CONSTRAINT `itemtypes_ibfk_1` FOREIGN KEY (`parent_type`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `itemtypes_ibfk_1` FOREIGN KEY (`parent_type`) REFERENCES `itemtypes` (`itemtype`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- 2.39.5