Mahnungs-Tabelle: Fremdschlüsselverknüpfung auf Rechnungstabelle

diff --git a/SL/DB/MetaSetup/Dunning.pm b/SL/DB/MetaSetup/Dunning.pm
index a0098e776bbbc323d40bfa1d22dea40654ed0f52..ee6ab4ef41c3e1c03755deb7596dcec84c6af8a6 100644 (file)
--- a/SL/DB/MetaSetup/Dunning.pm
+++ b/SL/DB/MetaSetup/Dunning.pm
@@ -33,10 +33,15 @@ __PACKAGE__->meta->foreign_keys(
     key_columns => { dunning_config_id => 'id' },
   },
 
-  fee_interest_ar => {
+  fee_interest_invoice => {
     class       => 'SL::DB::Invoice',
     key_columns => { fee_interest_ar_id => 'id' },
   },
+
+  invoice => {
+    class       => 'SL::DB::Invoice',
+    key_columns => { trans_id => 'id' },
+  },
 );
 
 1;

diff --git a/scripts/rose_auto_create_model.pl b/scripts/rose_auto_create_model.pl
index 5a0f7c23ca35bdf44bb855293ce88c9e2d10ee87..2ab34cb90d38b3db92463d2d5426a798ffa995a0 100755 (executable)
--- a/scripts/rose_auto_create_model.pl
+++ b/scripts/rose_auto_create_model.pl
@@ -82,6 +82,8 @@ our %foreign_key_name_map     = (
 
     assembly                  => { parts_id => 'part', id => 'assembly_part' },
     assortment_items          => { parts_id => 'part' },
+
+    dunning                   => { trans_id => 'invoice', fee_interest_ar_id => 'fee_interest_invoice' },
   },
 );
 

diff --git a/sql/Pg-upgrade2/dunning_foreign_key_for_trans_id.sql b/sql/Pg-upgrade2/dunning_foreign_key_for_trans_id.sql
new file mode 100644 (file)
index 0000000..e84892e
--- /dev/null
+++ b/sql/Pg-upgrade2/dunning_foreign_key_for_trans_id.sql
@@ -0,0 +1,14 @@
+-- @tag: dunning_foreign_key_for_trans_id
+-- @description: Mahnungstabelle: Fremdschlüssel für Verknüpfung zur Rechnungstabelle
+-- @depends: release_3_5_3
+DELETE FROM dunning
+WHERE NOT EXISTS (
+  SELECT ar.id
+  FROM ar
+  WHERE ar.id = dunning.trans_id
+);
+
+ALTER TABLE dunning
+ADD CONSTRAINT dunning_trans_id_fkey
+FOREIGN KEY (trans_id) REFERENCES ar (id)
+ON DELETE CASCADE;