Attachment #171017 for bug #18705

View | Details | Raw Unified | Return to bug 18705
Collapse All | Expand All




use Modern::Perl;

return {
    bug_number  => "18705",
    description => "Add foreign key on column itemnumber in collections_tracking table",
    up          => sub {
        my ($args) = @_;
        my ( $dbh, $out ) = @$args{qw(dbh out)};

        $dbh->do(
            q{DELETE FROM collections_tracking WHERE itemnumber NOT IN (SELECT itemnumber FROM items WHERE items.itemnumber = collections_tracking.itemnumber)}
        );
        $dbh->do(q{ALTER TABLE collections_tracking ADD KEY `collectionst_ibfk_2` (`itemnumber`)});
        $dbh->do(
            q{ALTER TABLE collections_tracking ADD CONSTRAINT FOREIGN KEY `collectionst_ibfk_2` (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE}
        );
    },
};

Lines 2178-2184 CREATE TABLE `collections_tracking` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-2 / +3 lines)
2178	`itemnumber` int(11) NOT NULL DEFAULT 0 COMMENT 'items.itemnumber',	2178	`itemnumber` int(11) NOT NULL DEFAULT 0 COMMENT 'items.itemnumber',
2179	PRIMARY KEY (`collections_tracking_id`),	2179	PRIMARY KEY (`collections_tracking_id`),
2180	KEY `collectionst_ibfk_1` (`colId`),	2180	KEY `collectionst_ibfk_1` (`colId`),
2181	CONSTRAINT `collectionst_ibfk_1` FOREIGN KEY (`colId`) REFERENCES `collections` (`colId`) ON DELETE CASCADE ON UPDATE CASCADE	2181	KEY `collectionst_ibfk_2` (`itemnumber`),
		2182	CONSTRAINT `collectionst_ibfk_1` FOREIGN KEY (`colId`) REFERENCES `collections` (`colId`) ON DELETE CASCADE ON UPDATE CASCADE,
		2183	CONSTRAINT `collectionst_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
2182	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	2184	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2183	/!40101 SET character_set_client = @saved_cs_client /;	2185	/!40101 SET character_set_client = @saved_cs_client /;
2184		2186
2185	-

Return to bug 18705

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug-18705-foreign-key-on-collections_tracking.pl (+18 lines)
	1	use Modern::Perl;
	2
	3	return {
	4	bug_number => "18705",
	5	description => "Add foreign key on column itemnumber in collections_tracking table",
	6	up => sub {
	7	my ($args) = @_;
	8	my ( $dbh, $out ) = @$args{qw(dbh out)};
	9
	10	$dbh->do(
	11	q{DELETE FROM collections_tracking WHERE itemnumber NOT IN (SELECT itemnumber FROM items WHERE items.itemnumber = collections_tracking.itemnumber)}
	12	);
	13	$dbh->do(q{ALTER TABLE collections_tracking ADD KEY `collectionst_ibfk_2` (`itemnumber`)});
	14	$dbh->do(
	15	q{ALTER TABLE collections_tracking ADD CONSTRAINT FOREIGN KEY `collectionst_ibfk_2` (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE}
	16	);
	17	},
	18	};