@@ -, +, @@ updatadatabase --- installer/data/mysql/updatedatabase.pl | 33 ++++++++++++-------------------- 1 file changed, 12 insertions(+), 21 deletions(-) --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -9812,7 +9812,7 @@ if ( CheckVersion($DBversion) ) { }); $dbh->do(" - CREATE TABLE IF NOT EXISTS account_credits ( + CREATE TABLE account_credits ( credit_id int(11) NOT NULL AUTO_INCREMENT, borrowernumber int(11) NOT NULL, `type` varchar(255) NOT NULL, @@ -9827,11 +9827,14 @@ if ( CheckVersion($DBversion) ) { updated_on timestamp NULL DEFAULT NULL, PRIMARY KEY (credit_id), KEY borrowernumber (borrowernumber), - KEY branchcode (branchcode) + KEY branchcode (branchcode), + FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; "); + $dbh->do(" - CREATE TABLE IF NOT EXISTS account_debits ( + CREATE TABLE account_debits ( debit_id int(11) NOT NULL AUTO_INCREMENT, borrowernumber int(11) NOT NULL DEFAULT '0', itemnumber int(11) DEFAULT NULL, @@ -9852,7 +9855,9 @@ if ( CheckVersion($DBversion) ) { KEY itemnumber (itemnumber), KEY borrowernumber (borrowernumber), KEY issue_id (issue_id), - KEY branchcode (branchcode) + KEY branchcode (branchcode), + FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; "); @@ -9866,27 +9871,13 @@ if ( CheckVersion($DBversion) ) { created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (offset_id), KEY fee_id (debit_id), - KEY payment_id (credit_id) + KEY payment_id (credit_id), + FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; "); $dbh->do(" - ALTER TABLE `account_credits` - ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, - ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE; - "); - $dbh->do(" - ALTER TABLE `account_debits` - ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, - ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE; - "); - $dbh->do(" - ALTER TABLE `account_offsets` - ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE, - ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE; - "); - - $dbh->do(" ALTER TABLE borrowers ADD account_balance DECIMAL( 28, 6 ) NOT NULL; "); --