From 407db0917597829f524ada4a7a23c8155e0a1aa6 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 29 Oct 2015 14:55:23 +0000 Subject: [PATCH] Bug 15084: DB Changes - add currency.archived and FK on aqorders.currency See the comment for details. Signed-off-by: Josef Moravec Signed-off-by: Kyle M Hall --- .../data/mysql/atomicupdate/bug_15084-add_fk_for_currency.sql | 8 ++++++++ 1 file changed, 8 insertions(+) create mode 100644 installer/data/mysql/atomicupdate/bug_15084-add_fk_for_currency.sql diff --git a/installer/data/mysql/atomicupdate/bug_15084-add_fk_for_currency.sql b/installer/data/mysql/atomicupdate/bug_15084-add_fk_for_currency.sql new file mode 100644 index 0000000..d3e00c1 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_15084-add_fk_for_currency.sql @@ -0,0 +1,8 @@ +-- Add the new currency.archived column +alter table currency add column archived tinyint(1) default 0; +-- Set currency=NULL if empty (just in case) +update aqorders set currency=NULL where currency=""; +-- Insert the missing currency and mark them as archived before adding the FK +insert into currency(currency, archived) select distinct currency, 1 from aqorders where currency not in (select currency from currency); +-- And finally add the FK +alter table aqorders add foreign key (currency) references currency(currency) on delete set null on update set null; -- 2.7.0