From 773e69a6461b6c08b9661caab3320b5ea18997a2 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 29 Aug 2016 14:07:27 +0100 Subject: [PATCH] Bug 17216: Add FK on mss.authorised_value --- installer/data/mysql/atomicupdate/bug_17216_2_add_fk.sql | 7 +++++++ installer/data/mysql/kohastructure.sql | 5 +++-- 2 files changed, 10 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_17216_2_add_fk.sql diff --git a/installer/data/mysql/atomicupdate/bug_17216_2_add_fk.sql b/installer/data/mysql/atomicupdate/bug_17216_2_add_fk.sql new file mode 100644 index 0000000..22b9ceb --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_17216_2_add_fk.sql @@ -0,0 +1,7 @@ +INSERT IGNORE INTO authorised_value_categories( category_name ) SELECT DISTINCT(authorised_value) FROM marc_subfield_structure; + +UPDATE marc_subfield_structure SET authorised_value = NULL WHERE authorised_value = ';'; + +ALTER TABLE marc_subfield_structure + MODIFY COLUMN authorised_value VARCHAR(32) DEFAULT NULL, + ADD CONSTRAINT marc_subfield_structure_ibfk_1 FOREIGN KEY (authorised_value) REFERENCES authorised_value_categories (category_name) ON UPDATE CASCADE ON DELETE SET NULL; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index a59598e..46f0828 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1118,7 +1118,7 @@ CREATE TABLE `marc_subfield_structure` ( `mandatory` tinyint(4) NOT NULL default 0, `kohafield` varchar(40) default NULL, `tab` tinyint(1) default NULL, - `authorised_value` varchar(20) default NULL, + `authorised_value` varchar(32) default NULL, `authtypecode` varchar(20) default NULL, `value_builder` varchar(80) default NULL, `isurl` tinyint(1) default NULL, @@ -1131,7 +1131,8 @@ CREATE TABLE `marc_subfield_structure` ( PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`), KEY `kohafield_2` (`kohafield`), KEY `tab` (`frameworkcode`,`tab`), - KEY `kohafield` (`frameworkcode`,`kohafield`) + KEY `kohafield` (`frameworkcode`,`kohafield`), + CONSTRAINT `marc_subfield_structure_ibfk_1` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- 2.8.1