From e8e3d782004b6449563b53eca5a1f9a94e0b11c8 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 13 Jan 2016 13:49:19 +0000 Subject: [PATCH] Bug 14849: Add foreign key on uploaded_files.owner Content-Type: text/plain; charset=utf-8 uploaded_files.owner refers to a borrowernumber, the DB structure should reflect this constraint. Test plan: 0/ Do not execute the update DB entry 1/ Add a new patron 2/ Use this patron to upload a new file 3/ In the uploaded_files you should see that the owner value is the borrowernumber of the newly created patron 4/ Execute the DB entry 5/ The uploaded_files.owner value should have been set to NULL 6/ Repeat 1, 2 and 3 7/ Delete the patron 5/ The uploaded_files.owner value should have been set to NULL Signed-off-by: Chris Cormack Signed-off-by: Marcel de Rooy The test plan seems to miss one step: 3b Delete the patron. --- installer/data/mysql/atomicupdate/bug_14849.sql | 2 ++ installer/data/mysql/kohastructure.sql | 3 ++- 2 files changed, 4 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/bug_14849.sql diff --git a/installer/data/mysql/atomicupdate/bug_14849.sql b/installer/data/mysql/atomicupdate/bug_14849.sql new file mode 100644 index 0000000..2aad752 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_14849.sql @@ -0,0 +1,2 @@ +UPDATE uploaded_files LEFT JOIN borrowers on uploaded_files.owner=borrowers.borrowernumber SET owner=NULL WHERE borrowers.borrowernumber IS NULL; +ALTER TABLE uploaded_files ADD FOREIGN KEY (owner) REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 431a541..bf28e81 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3372,7 +3372,8 @@ CREATE TABLE uploaded_files ( owner int(11), public tinyint, permanent tinyint, - PRIMARY KEY (id) + PRIMARY KEY (id), + CONSTRAINT `uploaded_files_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- 1.7.10.4