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

(-)a/installer/data/mysql/atomicupdate/16170_dbrev.sql (+25 lines)
Line 0 Link Here
1
my $dbh = C4::Context->dbh;
2
my $DBversion = "XXX";
3
4
# RM: Copy/paste from here, and uncomment
5
6
#if ( CheckVersion($DBversion) ) {
7
    # this should normally not be needed, but just in case
8
    my ( $cnt ) = $dbh->selectrow_array( q|
9
SELECT COUNT(*) FROM items it
10
LEFT JOIN biblio bi ON bi.biblionumber=it.biblionumber
11
LEFT JOIN biblioitems bii USING (biblioitemnumber)
12
WHERE bi.biblionumber IS NULL
13
    |);
14
    if( $cnt ) {
15
        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";
16
    } else {
17
        # now add FK
18
        $dbh->do( q|
19
ALTER TABLE items
20
ADD FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
21
        |);
22
        print "Upgrade to $DBversion done (Bug 1xxxx - Add FK for biblionumber in items)\n";
23
    }
24
    #SetVersion($DBversion);
25
#}
(-)a/installer/data/mysql/kohastructure.sql (-2 / +2 lines)
Lines 1282-1288 CREATE TABLE `items` ( -- holdings/item information Link Here
1282
  KEY `itype_idx` (`itype`),
1282
  KEY `itype_idx` (`itype`),
1283
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1283
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1284
  CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1284
  CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1285
  CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1285
  CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1286
  CONSTRAINT `items_ibfk_4` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1286
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1287
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1287
1288
1288
--
1289
--
1289
- 

Return to bug 16170