1 -- @tag: oe_delivery_orders_foreign_keys
2 -- @description: Fremdschlüsseldefinitionen für oe und delivery_orders
3 -- @depends: release_3_0_0
5 ALTER TABLE oe ALTER COLUMN department_id DROP DEFAULT;
7 UPDATE oe SET cp_id = NULL WHERE (cp_id IS NOT NULL) AND (cp_id NOT IN (SELECT cp_id FROM contacts));
8 UPDATE oe SET delivery_customer_id = NULL WHERE (delivery_customer_id IS NOT NULL) AND (delivery_customer_id NOT IN (SELECT id FROM customer));
9 UPDATE oe SET delivery_vendor_id = NULL WHERE (delivery_vendor_id IS NOT NULL) AND (delivery_vendor_id NOT IN (SELECT id FROM vendor));
10 UPDATE oe SET department_id = NULL WHERE (department_id IS NOT NULL) AND (department_id NOT IN (SELECT id FROM department));
11 UPDATE oe SET language_id = NULL WHERE (language_id IS NOT NULL) AND (language_id NOT IN (SELECT id FROM language));
12 UPDATE oe SET payment_id = NULL WHERE (payment_id IS NOT NULL) AND (payment_id NOT IN (SELECT id FROM payment_terms));
13 UPDATE oe SET shipto_id = NULL WHERE (shipto_id IS NOT NULL) AND (shipto_id NOT IN (SELECT shipto_id FROM shipto));
15 UPDATE delivery_orders SET department_id = NULL WHERE (department_id IS NOT NULL) AND (department_id NOT IN (SELECT id FROM department));
16 UPDATE delivery_orders SET shipto_id = NULL WHERE (shipto_id IS NOT NULL) AND (shipto_id NOT IN (SELECT shipto_id FROM shipto));
18 ALTER TABLE oe ADD FOREIGN KEY (cp_id) REFERENCES contacts (cp_id);
19 ALTER TABLE oe ADD FOREIGN KEY (delivery_customer_id) REFERENCES customer (id);
20 ALTER TABLE oe ADD FOREIGN KEY (delivery_vendor_id) REFERENCES vendor (id);
21 ALTER TABLE oe ADD FOREIGN KEY (department_id) REFERENCES department (id);
22 ALTER TABLE oe ADD FOREIGN KEY (language_id) REFERENCES language (id);
23 ALTER TABLE oe ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id);
24 ALTER TABLE oe ADD FOREIGN KEY (shipto_id) REFERENCES shipto (shipto_id);
26 ALTER TABLE delivery_orders ADD FOREIGN KEY (department_id) REFERENCES department (id);
27 ALTER TABLE delivery_orders ADD FOREIGN KEY (shipto_id) REFERENCES shipto (shipto_id);