Diverse Fremdschlüssel für Tabellen ar und ap

[mfinanz.git] / sql / Pg-upgrade2 / ar_ap_foreign_keys.sql

diff --git a/sql/Pg-upgrade2/ar_ap_foreign_keys.sql b/sql/Pg-upgrade2/ar_ap_foreign_keys.sql
new file mode 100644 (file)
index 0000000..cd813de
--- /dev/null
+++ b/sql/Pg-upgrade2/ar_ap_foreign_keys.sql
@@ -0,0 +1,32 @@
+-- @tag: ar_ap_foreign_keys
+-- @description: Fremdschlüsselverweise für diverse Spalten in ar und ap
+-- @depends: release_3_0_0
+-- @charset: utf-8
+ALTER TABLE ar ALTER COLUMN department_id DROP DEFAULT;
+ALTER TABLE ap ALTER COLUMN department_id DROP DEFAULT;
+
+UPDATE ar SET cp_id         = NULL WHERE (cp_id         IS NOT NULL) AND (cp_id         NOT IN (SELECT cp_id     FROM contacts));
+UPDATE ar SET department_id = NULL WHERE (department_id IS NOT NULL) AND (department_id NOT IN (SELECT id        FROM department));
+UPDATE ar SET employee_id   = NULL WHERE (employee_id   IS NOT NULL) AND (employee_id   NOT IN (SELECT id        FROM employee));
+UPDATE ar SET language_id   = NULL WHERE (language_id   IS NOT NULL) AND (language_id   NOT IN (SELECT id        FROM language));
+UPDATE ar SET payment_id    = NULL WHERE (payment_id    IS NOT NULL) AND (payment_id    NOT IN (SELECT id        FROM payment_terms));
+UPDATE ar SET shipto_id     = NULL WHERE (shipto_id     IS NOT NULL) AND (shipto_id     NOT IN (SELECT shipto_id FROM shipto));
+
+UPDATE ap SET cp_id         = NULL WHERE (cp_id         IS NOT NULL) AND (cp_id         NOT IN (SELECT cp_id     FROM contacts));
+UPDATE ap SET department_id = NULL WHERE (department_id IS NOT NULL) AND (department_id NOT IN (SELECT id        FROM department));
+UPDATE ap SET employee_id   = NULL WHERE (employee_id   IS NOT NULL) AND (employee_id   NOT IN (SELECT id        FROM employee));
+UPDATE ap SET language_id   = NULL WHERE (language_id   IS NOT NULL) AND (language_id   NOT IN (SELECT id        FROM language));
+UPDATE ap SET payment_id    = NULL WHERE (payment_id    IS NOT NULL) AND (payment_id    NOT IN (SELECT id        FROM payment_terms));
+
+ALTER TABLE ar ADD FOREIGN KEY (cp_id)         REFERENCES contacts      (cp_id);
+ALTER TABLE ar ADD FOREIGN KEY (department_id) REFERENCES department    (id);
+ALTER TABLE ar ADD FOREIGN KEY (employee_id)   REFERENCES employee      (id);
+ALTER TABLE ar ADD FOREIGN KEY (language_id)   REFERENCES language      (id);
+ALTER TABLE ar ADD FOREIGN KEY (payment_id)    REFERENCES payment_terms (id);
+ALTER TABLE ar ADD FOREIGN KEY (shipto_id)     REFERENCES shipto        (shipto_id);
+
+ALTER TABLE ap ADD FOREIGN KEY (cp_id)         REFERENCES contacts      (cp_id);
+ALTER TABLE ap ADD FOREIGN KEY (employee_id)   REFERENCES employee      (id);
+ALTER TABLE ap ADD FOREIGN KEY (department_id) REFERENCES department    (id);
+ALTER TABLE ap ADD FOREIGN KEY (language_id)   REFERENCES language      (id);
+ALTER TABLE ap ADD FOREIGN KEY (payment_id)    REFERENCES payment_terms (id);