Constraints auf vendor/customer: Vorher alle verwaisten Einträge entfernen

diff --git a/sql/Pg-upgrade2/customer_add_constraints.sql b/sql/Pg-upgrade2/customer_add_constraints.sql
index 89ca9d62854f06cf285ab3f702320494aaa281f6..65d3386454bd12ec5de385cfb782a307d5d6487b 100644 (file)
--- a/sql/Pg-upgrade2/customer_add_constraints.sql
+++ b/sql/Pg-upgrade2/customer_add_constraints.sql
@@ -4,8 +4,10 @@
 -- @charset: utf-8
 -- @ignore: 0
 
 -- @charset: utf-8
 -- @ignore: 0
 

--- verwaiste Zahlungsbedingungen vorher entfernen:
-update customer set payment_id = NULL where payment_id not in (select id from payment_terms);
+-- verwaiste Einträge vorher entfernen
+UPDATE customer SET payment_id  = NULL WHERE payment_id  NOT IN (SELECT id FROM payment_terms);
+UPDATE customer SET language_id = NULL WHERE language_id NOT IN (SELECT id FROM language);
+UPDATE customer SET business_id = NULL WHERE business_id NOT IN (SELECT id FROM business);

 
 ALTER TABLE customer ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id);
 ALTER TABLE customer ADD FOREIGN KEY (language_id) REFERENCES language (id);
 
 ALTER TABLE customer ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id);
 ALTER TABLE customer ADD FOREIGN KEY (language_id) REFERENCES language (id);

diff --git a/sql/Pg-upgrade2/vendor_add_constraints.sql b/sql/Pg-upgrade2/vendor_add_constraints.sql
index 3d3a81657cfbed6856af7de159847f85ae9f5359..4439377b3f82c2c1175189b20b8d5c03934ccc86 100644 (file)
--- a/sql/Pg-upgrade2/vendor_add_constraints.sql
+++ b/sql/Pg-upgrade2/vendor_add_constraints.sql
@@ -4,9 +4,10 @@
 -- @charset: utf-8
 -- @ignore: 0
 
 -- @charset: utf-8
 -- @ignore: 0
 

--- verwaiste Zahlungsbedingungen vorher entfernen
-update vendor set payment_id = NULL where payment_id not in (select id from payment_terms);
-
+-- verwaiste Einträge vorher entfernen
+UPDATE vendor SET payment_id  = NULL WHERE payment_id  NOT IN (SELECT id FROM payment_terms);
+UPDATE vendor SET language_id = NULL WHERE language_id NOT IN (SELECT id FROM language);
+UPDATE vendor SET business_id = NULL WHERE business_id NOT IN (SELECT id FROM business);

 
 ALTER TABLE vendor ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id);
 ALTER TABLE vendor ADD FOREIGN KEY (language_id) REFERENCES language (id);
 
 ALTER TABLE vendor ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id);
 ALTER TABLE vendor ADD FOREIGN KEY (language_id) REFERENCES language (id);