FB: Kopie eines Datensatzes in Tabelle mit unbekannter Struktur erzeugen

[RSE]

Ich will eine PSQL-Procedure schreiben, die für eine Tabelle einen Wert ändert, der in vielen weiteren Tabellen als Foreign Key "verlinkt" ist. Deshalb ist der Ansatz, den shmia verlinkt hat, nicht umsetzbar.

Beispiel:
Tab0:

Code:

ID
1
2
3
5

Tab1 (FK: ID references Tab0.ID):

Code:

ID   Wert
1   bla
2   foo
5   foo

Tab2 (FK: ID references Tab1.ID):

Code:

ID   Zusatzwert
2   zweiundvierzig
5   foobar

OT: Wie kann man hier Tabellen ordentlich formatieren?

Tab0.ID = 5 ist der doppelte Datensatz zum Original Tab0.ID = 3. Tab0.ID = 5 soll also gelöscht werden, alle Referenzen auf Tab0.ID = 5 sollen umgebogen werden auf Tab0.ID = 3. Tab0.ID = 3 hat in weiteren hier nicht dargestellten Tabellen Referenzen/Abhängigkeiten, so dass Tab0.ID = 3 nicht gelöscht werden kann, um Tab0.ID = 5 einfach zu ändern in Tab0.ID = 3. Ergebnis soll folgendes sein:
Tab0:

Code:

ID
1
2
3

Tab1 (FK: ID references Tab0.ID):

Code:

ID   Wert
1   bla
2   foo
3   foo

Tab2 (FK: ID references Tab1.ID):

Code:

ID   Zusatzwert
2   zweiundvierzig
3   foobar

Um dieses Ergebnis zu erreichen müssen in Tab1 und Tab2 Datensätze geändert werden, die abhängig voneinander sind, das geht nur über den Zwischenschritt eines temporären Datensatzes (Erklärung siehe erster Post).
Meine Prozedur: ChangeForeignKeys(Tabelle, Feld, WertAlt, WertNeu);
Eingabe im Beispiel für meine Prozedur: ChangeForeignKeys(Tab0, ID, 5, 3);
Die Prozedur iteriert über alle Foreign Keys anderer Tabellen in der DB, die auf Tab0.ID referenzieren (auch über mehrere Schritte).

Es geht um das Kopieren eines Datensatzes in einer Tabelle, dessen Struktur ich nicht näher kenne. Was ich über die Struktur herausfinden muss, kann ich in den Systemtabellen herausfinden, das Prinzip muss aber dynamisch sein, damit es auf andere Strukturen/andere Tabellen ebenso angewendet werden kann. Der Datensatz muss also komplett kopiert werden, dabei muss genau der Primary Key und das zu ändernde Feld (im Beispiel ID) geändert werden.

Mein aktueller (vielversprechender) Ansatz sieht so aus:

SQL-Code:

/*
str1, str2: temporäre strings
fkrel1: entspricht im Beispiel Tab1
fkfield1: entspricht im Beispiel ID
*/
      str1 = '';
      for select rdb$field_name
            from rdb$relation_fields 
            where rdb$relation_name = :fkrel1
            order by -- unfertig - Ordnung ist wichtig!
        into str2 do
      begin
        if (str1 <> '') then
          str1 = str1 || ', ';
        -- Eine Änderung aller uniques muss noch umgesetzt werden
        if (str2 = fkfield1) then
          str1 = str1 || '2'; -- hier muss ein sinniger temporärer Wert rein
        else
          str1 = str1 || str2;
      end
      execute statement 'insert into ' || fkrel1 ||
                        '  select ' || str1 ||
                        '    from ' || fkrel1 ||
                        '    where ' || fkfield1 || ' = ' || oldvalue;

[Olli73]

Zitat von RSE:

Tab1 (FK: ID references Tab0.ID):

Tab2 (FK: ID references Tab1.ID):

Du refernzierst beim FK in TAB2 auf den FK in TAB1 (und nicht auf einen PK von TAB1)?

Oder ist das eine 1:1(:1)-Beziehung mit PK=FK in Tab1 (und evtl. auch Tab2)?

Normalerweise hätte Tab1 einen eigenen PK (verdient) auf den in Tab2 refernziert wird und dann würde es genügen den FK in Tab1 anzupassen und alles was danachkommt (Tab2, ...) wäre nicht betroffen...

[RSE]

In diesem Beispiel wäre eine flachere Hierarchie durchaus denkbar, aber in der Praxis leider nicht. Ich komme um mehrstufige Abhängigkeiten nicht drumherum. In der Praxis habe ich z.B. Einrichtungen (Tab0, EinrID) mit Mitarbeitern (Tab1 mit FK (EinrID) auf Tab0, PK ist EinrID + MAID), die im Bestellwesen referenziert werden (Tab2 mit FK auf PK von Tab1 (EinrID + MAID)). Wenn sich also die EinrID wie im Beispiel ändert, wirkt sich das bis zu dem MA im Bestellwesen aus, eine flachere Abbildung ist logisch nicht sinnvoll.

[Sir Rufo]

(das war gerade mist)

Wenn du in den direkt referenzierten Tabellen ( Tab0 -> Tab1 ) den Wert änderst, dann sollten die anderen Werte automatisch nachfolgen.

In diesem Beispiel sollte also folgendes ausreichend sein:

Code:

UPDATE
  Tab1
SET
  ID = 3
WHERE
  ID = 5;

DELETE FROM
  Tab0
WHERE
  ID = 5;

[Olli73]

Zitat von RSE:

In diesem Beispiel wäre eine flachere Hierarchie durchaus denkbar, aber in der Praxis leider nicht. Ich komme um mehrstufige Abhängigkeiten nicht drumherum. In der Praxis habe ich z.B. Einrichtungen (Tab0, EinrID) mit Mitarbeitern (Tab1 mit FK (EinrID) auf Tab0, PK ist EinrID + MAID), die im Bestellwesen referenziert werden (Tab2 mit FK auf PK von Tab1 (EinrID + MAID)). Wenn sich also die EinrID wie im Beispiel ändert, wirkt sich das bis zu dem MA im Bestellwesen aus, eine flachere Abbildung ist logisch nicht sinnvoll.

Also ich mache das i.d.R. (es gibt Ausnahmen, wo es Sinn macht) so, dass dann in Tab1 der PK die MAID (alleine) ist und EinrID dort nur der FK; in Tab2 wird dann nur auf MAID referenziert. Vom Bestellwesen kommt man dann natürlich nur noch auf die Enrichtung, wenn die Mitarbeiter-Tabelle "zwischenschaltet", aber dafür minimiert es die Abhängigkeiten mit den IDs und ich brauche immer nur 1 Datenfeld für die Verknüfung der Tabellen.

[Blup]

Der Vorschlag von Olli73 sieht doch gar keine flachere Struktur vor.
Nur die Rolle die der Mitarbeiter einer Einrichtung einnimmt, sollte eine eigen ID bekommen...

Tabelle1
T1.ID PK

Tabelle2
T2.ID PK

Tabelle3 {Verknüpfung zwischen 1 und 2}
T3.ID PK
T1.ID FK UNQ
T2.ID FK UNQ

Tabelle4 {z.B. Bestellungen der Rolle in Tabelle3
T4.ID PK
T3.ID FK

Insbesondere wenn man mit einem OPF arbeitet, wird meist nur ein Feld als PK angenommen.

@Sir Rufo
Das funktioniert nicht, wenn ein Feld sowohl mit FK verknüpft ist und gleichzeitig Teil eines PK ist.

[Sir Rufo]

Zitat von Blup:

@Sir Rufo
Das funktioniert nicht, wenn ein Feld sowohl mit FK verknüpft ist und gleichzeitig Teil eines PK ist.

Ok, dann muss ich aber mal mit meinem MySQL Server schimpfen ... dann ist der wohl kaputt, denn der macht das genau so.

SQL Fiddle DEMO

Code:

CREATE TABLE `tab0` (
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `tab1` (
  `id` int(11) unsigned NOT NULL,
  `Wert` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_tab1_tab0` FOREIGN KEY (`id`) REFERENCES `tab0` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tab2` (
  `id` int(11) unsigned NOT NULL,
  `Zusatzwert` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_tab2_tab1` FOREIGN KEY (`id`) REFERENCES `tab1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tab0 ( id ) VALUES (1),(2),(3),(5);
INSERT INTO tab1 ( id, wert ) VALUES (1,'bla'),(2,'foo'),(5,'foo');
INSERT INTO tab2 ( id, zusatzwert ) VALUES (2,'zweiundvierzig'),(5,'foobar');

UPDATE
  Tab1
SET
  ID = 3
WHERE
  ID = 5;

DELETE FROM
  Tab0
WHERE
  ID = 5;

[Blup]

Das ist zwar nicht die Situation die ich meinte, hab mich aber überzeugt das es doch funktioniert.
Ich nehm alles zurück und behaupte das Gegenteil.

[RSE]

Bei mir schlägt da irgendwas quer mit dem ON UPDATE CASCADE. Ich guck mir das morgen nochmal genauer an woran das liegt. Jetzt ist erstmal Feierabend.