From 20dce153b1f785a758d6924103e51a6cef173fd3 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Mon, 1 Nov 2010 08:23:35 -0400 Subject: [PATCH] bug 5308: add foreign key constraints as well Follows up on previous patch to require that routing list entries be linked to subscriptions and patrons. Signed-off-by: Galen Charlton --- installer/data/mysql/kohastructure.sql | 6 +++++- installer/data/mysql/updatedatabase.pl | 8 ++++++++ 2 files changed, 13 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 51e4c88860..9148970ca8 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1795,7 +1795,11 @@ CREATE TABLE `subscriptionroutinglist` ( `ranking` int(11) default NULL, `subscriptionid` int(11) NOT NULL, PRIMARY KEY (`routingid`), - UNIQUE (`subscriptionid`, `borrowernumber`) + UNIQUE (`subscriptionid`, `borrowernumber`), + CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) + ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index ddf3fa5ca4..6443c67ca2 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -3814,6 +3814,14 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { $dbh->do("DELETE FROM subscriptionroutinglist WHERE routingid IN (SELECT routingid FROM del_subscriptionroutinglist);"); $dbh->do("ALTER TABLE subscriptionroutinglist ADD UNIQUE (subscriptionid, borrowernumber);"); + $dbh->do("ALTER TABLE subscriptionroutinglist + ADD CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) + REFERENCES `borrowers` (`borrowernumber`) + ON DELETE CASCADE ON UPDATE CASCADE"); + $dbh->do("ALTER TABLE subscriptionroutinglist + ADD CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) + REFERENCES `subscription` (`subscriptionid`) + ON DELETE CASCADE ON UPDATE CASCADE"); print "Upgrade to $DBversion done (Make subscriptionroutinglist more strict)\n"; SetVersion ($DBversion); } -- 2.39.5