1 -- @tag: ar_ap_foreign_keys
2 -- @description: Fremdschlüsselverweise für diverse Spalten in ar und ap
3 -- @depends: release_3_0_0
5 ALTER TABLE ar ALTER COLUMN department_id DROP DEFAULT;
6 ALTER TABLE ap ALTER COLUMN department_id DROP DEFAULT;
8 UPDATE ar SET cp_id = NULL WHERE (cp_id IS NOT NULL) AND (cp_id NOT IN (SELECT cp_id FROM contacts));
9 UPDATE ar SET department_id = NULL WHERE (department_id IS NOT NULL) AND (department_id NOT IN (SELECT id FROM department));
10 UPDATE ar SET employee_id = NULL WHERE (employee_id IS NOT NULL) AND (employee_id NOT IN (SELECT id FROM employee));
11 UPDATE ar SET language_id = NULL WHERE (language_id IS NOT NULL) AND (language_id NOT IN (SELECT id FROM language));
12 UPDATE ar SET payment_id = NULL WHERE (payment_id IS NOT NULL) AND (payment_id NOT IN (SELECT id FROM payment_terms));
13 UPDATE ar SET shipto_id = NULL WHERE (shipto_id IS NOT NULL) AND (shipto_id NOT IN (SELECT shipto_id FROM shipto));
15 UPDATE ap SET cp_id = NULL WHERE (cp_id IS NOT NULL) AND (cp_id NOT IN (SELECT cp_id FROM contacts));
16 UPDATE ap SET department_id = NULL WHERE (department_id IS NOT NULL) AND (department_id NOT IN (SELECT id FROM department));
17 UPDATE ap SET employee_id = NULL WHERE (employee_id IS NOT NULL) AND (employee_id NOT IN (SELECT id FROM employee));
18 UPDATE ap SET language_id = NULL WHERE (language_id IS NOT NULL) AND (language_id NOT IN (SELECT id FROM language));
19 UPDATE ap SET payment_id = NULL WHERE (payment_id IS NOT NULL) AND (payment_id NOT IN (SELECT id FROM payment_terms));
21 ALTER TABLE ar ADD FOREIGN KEY (cp_id) REFERENCES contacts (cp_id);
22 ALTER TABLE ar ADD FOREIGN KEY (department_id) REFERENCES department (id);
23 ALTER TABLE ar ADD FOREIGN KEY (employee_id) REFERENCES employee (id);
24 ALTER TABLE ar ADD FOREIGN KEY (language_id) REFERENCES language (id);
25 ALTER TABLE ar ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id);
26 ALTER TABLE ar ADD FOREIGN KEY (shipto_id) REFERENCES shipto (shipto_id);
28 ALTER TABLE ap ADD FOREIGN KEY (cp_id) REFERENCES contacts (cp_id);
29 ALTER TABLE ap ADD FOREIGN KEY (employee_id) REFERENCES employee (id);
30 ALTER TABLE ap ADD FOREIGN KEY (department_id) REFERENCES department (id);
31 ALTER TABLE ap ADD FOREIGN KEY (language_id) REFERENCES language (id);
32 ALTER TABLE ap ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id);