From 8e788af50b80283dc381777891f7db0a783b0ea3 Mon Sep 17 00:00:00 2001 From: Emily Lamancusa Date: Fri, 7 Jun 2024 16:24:26 -0400 Subject: [PATCH] Bug 37000: (follow-up) Add foreign key last Certain configurations of MySQL will not allow a column to be changed from nullable to non-nullable if the column has a foreign key constraint. Add the foreign key constraint last to avoid issues from this. Signed-off-by: Martin Renvoize --- installer/data/mysql/db_revs/231200044.pl | 15 ++++++++++++--- 1 file changed, 12 insertions(+), 3 deletions(-) diff --git a/installer/data/mysql/db_revs/231200044.pl b/installer/data/mysql/db_revs/231200044.pl index af6b69966d..7db75f74f0 100755 --- a/installer/data/mysql/db_revs/231200044.pl +++ b/installer/data/mysql/db_revs/231200044.pl @@ -12,9 +12,8 @@ return { $dbh->do( q{ ALTER TABLE bookings - ADD COLUMN `pickup_library_id` varchar(10) DEFAULT NULL COMMENT 'Identifier for booking pickup library' AFTER `item_id`, - ADD CONSTRAINT `bookings_ibfk_4` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE - } + ADD COLUMN `pickup_library_id` varchar(10) DEFAULT NULL COMMENT 'Identifier for booking pickup library' AFTER `item_id` + } ) == 1 && say_success( $out, "Added column 'bookings.pickup_library_id'" ); @@ -59,5 +58,15 @@ return { } ) == 1 && say_success( $out, "Updated column 'bookings.pickup_library_id' to NOT NULL" ); } + + unless ( foreign_key_exists( 'bookings', 'bookings_ibfk_4' ) ) { + $dbh->do( + q{ + ALTER TABLE bookings + ADD CONSTRAINT `bookings_ibfk_4` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE + } + ) == 1 + && say_success( $out, "Added foreign key 'bookings_ibfk_4' to column 'bookings.pickup_library_id'" ); + } }, }; -- 2.39.5