@@ -, +, @@ unexisting record. Run the upgrade again. Notice the warning. --- installer/data/mysql/atomicupdate/16170_dbrev.perl | 25 ++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 3 ++- 2 files changed, 27 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/16170_dbrev.perl --- a/installer/data/mysql/atomicupdate/16170_dbrev.perl +++ a/installer/data/mysql/atomicupdate/16170_dbrev.perl @@ -0,0 +1,25 @@ +my $dbh = C4::Context->dbh; +my $DBversion = "XXX"; + +# RM: Copy/paste from here, and uncomment + +#if ( CheckVersion($DBversion) ) { + # this should normally not be needed, but just in case + my ( $cnt ) = $dbh->selectrow_array( q| +SELECT COUNT(*) FROM items it +LEFT JOIN biblio bi ON bi.biblionumber=it.biblionumber +LEFT JOIN biblioitems bii USING (biblioitemnumber) +WHERE bi.biblionumber IS NULL + |); + if( $cnt ) { + print "WARNING: You have corrupted data in your items table!! The table contains $cnt references to biblio records that do not exist.\nPlease correct your data IMMEDIATELY after this upgrade and manually add the foreign key constraint for biblionumber in the items table.\n"; + } else { + # now add FK + $dbh->do( q| +ALTER TABLE items +ADD FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE + |); + print "Upgrade to $DBversion done (Bug 1xxxx - Add FK for biblionumber in items)\n"; + } + #SetVersion($DBversion); +#} --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -1284,7 +1284,8 @@ CREATE TABLE `items` ( -- holdings/item information KEY `itype_idx` (`itype`), CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE, - CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE + CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE, + CONSTRAINT `items_ibfk_4` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- --