From ad3cc49e116f60e76254af06e59e5d4390ec3b1e Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Fri, 23 Apr 2021 09:36:03 -0400 Subject: [PATCH] Bug 17202: (QA follow-up) Add foreign key constraint Signed-off-by: Kyle M Hall --- installer/data/mysql/atomicupdate/bug_17202.perl | 12 ++++++++++++ installer/data/mysql/kohastructure.sql | 3 ++- 2 files changed, 14 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/bug_17202.perl diff --git a/installer/data/mysql/atomicupdate/bug_17202.perl b/installer/data/mysql/atomicupdate/bug_17202.perl new file mode 100644 index 0000000000..875675b90f --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_17202.perl @@ -0,0 +1,12 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + $dbh->do(q{ + DELETE FROM collections_tracking WHERE colId NOT IN ( SELECT colId FROM collections ) + }); + $dbh->do(q{ + ALTER TABLE collections_tracking + ADD CONSTRAINT `collectionst_ibfk_1` FOREIGN KEY (`colId`) REFERENCES `collections` (`colId`) ON DELETE CASCADE ON UPDATE CASCADE + }); + + NewVersion( $DBversion, 17202, "Add FK constraint for collection to collections_tracking"); +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 16d2b6eb0d..aa39f89043 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1980,7 +1980,8 @@ CREATE TABLE `collections_tracking` ( `collections_tracking_id` int(11) NOT NULL AUTO_INCREMENT, `colId` int(11) NOT NULL DEFAULT 0 COMMENT 'collections.colId', `itemnumber` int(11) NOT NULL DEFAULT 0 COMMENT 'items.itemnumber', - PRIMARY KEY (`collections_tracking_id`) + PRIMARY KEY (`collections_tracking_id`), + CONSTRAINT `collectionst_ibfk_1` FOREIGN KEY (`colId`) REFERENCES `collections` (`colId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- 2.24.3 (Apple Git-128)