5 description => "Do not allow NULL values in branchcodes for reserves",
8 my ($dbh, $out) = @$args{qw(dbh out)};
10 my $sth = $dbh->prepare(q{
11 SELECT borrowernumber, biblionumber
13 WHERE branchcode IS NULL;
16 my $holds_no_branch = $sth->fetchall_arrayref( {} );
18 if ( scalar @{$holds_no_branch} > 0 ) {
19 say $out "Holds with no branchcode were found and will be updated to the first branch in the system";
20 foreach my $hnb ( @{$holds_no_branch} ) {
21 say $out "Please review hold for borrowernumber "
22 . $hnb->{borrowernumber}
24 . $hnb->{biblionumber}
25 . " to correct pickup branch if necessary";
29 # Ensure we have no NULL's in the branchcode field
31 UPDATE reserves SET branchcode = ( SELECT branchcode FROM branches LIMIT 1) WHERE branchcode IS NULL;
34 # Remove FOREIGN KEY CONSTRAINT
35 if ( foreign_key_exists( 'reserves', 'reserves_ibfk_4' ) ) {
37 ALTER TABLE reserves DROP FOREIGN KEY reserves_ibfk_4;
41 # Set the NOT NULL configuration
44 MODIFY COLUMN `branchcode` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'foreign key from the branches table defining which branch the patron wishes to pick this hold up at'
46 # Replace the constraint
48 ALTER TABLE reserves ADD CONSTRAINT reserves_ibfk_4 FOREIGN KEY (branchcode) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE;
51 # Print useful stuff here
52 say $out "Removed NULL option from branchcode for reserves";