Firebird Reihenfolge der Tabellen bei Insert mit Foreignkey

[Kostas]

Hallo Zusammen,

wenn eine Firebird Datenbank ForeignKeys verwendet, muss bei Inserts auf die richtige Reihenfolge geachtet werden.
Gibt es eine Möglichkeit über die Systemtabellen die richtige Reihenfolge der Tabellen für die Inserts herauszufinden?

Gruß Kostas

[hoika]

Hallo,
ich glaube, wenn du alle Inserts in eine einzige Transaktion packst,
spielt die Reihenfolge keine Rolle.


Heiko

[Neutral General]

Du kennst doch das Datenbanklayout oder nicht und daher die richtige Reihenfolge in der gespeichert werden muss.
Was ist das Problem daran die Reihenfolge im Code zu ändern?

Klar kannst du die Systemtabellen durchlaufen und dir ne Reihenfolge zusammenbauen, aber du kennst die Reihenfolge doch.

[Jumpy]

Zitat:

Zitat von Neutral General (Beitrag 1339916)

Du kennst doch das Datenbanklayout oder nicht und daher die richtige Reihenfolge in der gespeichert werden muss.
Was ist das Problem daran die Reihenfolge im Code zu ändern?

Bin jetzt nicht der TE und prinzipiell hast du ja recht, aber wir betreuen auch eine Software, die knapp 2000 Tabellen in der Datenbank benötigt/hat. Und wenn dann mal Massendaten von Neukunden kommen o.ä. dann haben wir ein Tool, dass diese Daten in der richtigen Reihenfolge einliest und dann die vom TE geforderten Methoden nutzt (allerdings Oracle, drum hilfts hier nicht).
Und da die zugrundeliegende Software eine Fremdsoftware ist, kennen wir das Datenmodell nicht 100%tig und analysieren die Constraints-Auflistung von Oracle dafür.

[Kostas]

Hallo Zusammen,

danke für die Antworten.
Sicherlich kennen ich das Datenmodel und ich kann auch die richtige Reihenfolge zu Fuß angeben. Ich hatte gehofft das es eine Möglichkeit gibt die richtige Reihenfolge über die Systemtabellen abzufragen. Tools wie IBExpert machen das ja auch automatisch mit jeder Datenbank. Könnte natürlich sein dass das nicht automatisch geht.
Über die Systemtabellen kann ich abfragen welche Tabellen keine ForeignKey haben. Die können in beliebiger Reihenfolge bei Inserts verwendet werden.
Tabellen die FKs haben, (können auch mehrere FKs sein) müssen in einer Sortierliste. Die Tabellen die selbst nicht referenziert werden können für die Inserts verwendet werden.
Alle anderen müssen zuerst dessen Master-Tabelle zuerst importieren u.s.w. Das ist ein gewisser Auffand aber machbar.

Ich möchte nicht die Reihenfolge zu Fuß setzen müssen sondern ein Liste abrufen mit der Reihenfolge für Inserts. Für Deletes ist es wieder umgekehrt. Die Records der letzte Tabelle müssen zuerst gelöscht werden.

Gruß Kostas

[Fritzew]

Auf Basis von Firedac:

hatte ich mal für ein ähnliches InHouse Problem bei uns so gelöst:

Delphi-Quellcode:

procedure TdbMoveOld.Execute_copy_Work;
var
  lTables: Tstringlist;
  lTablesnoReference: Tstringlist;
  lTablesWithReference: Tstringlist;
  lTablesDummy: Tstringlist;
  lTablename: string;
  lfkey: string;

  lFinishedTables: Tstringlist;
  aindex: integer;

  function getIndexforinsert: integer;
  var
    dummy: Tstringlist;
    i, temp: integer;
    s: string;
    found: boolean;
  begin
    result := -1;
    for i := 0 to lTablesWithReference.count - 1 do
    begin
      dummy := pointer(lTablesWithReference.Objects[i]);

      if dummy <> nil then
      begin
        found := true;
        for s in dummy do
          if not lFinishedTables.find(s, temp) then
          begin
            found := false;
          end;
        if found then
          exit(i);
      end;

    end;
  end;

begin
  lTables := Tstringlist.create;
  lTablesnoReference := Tstringlist.create;
  lTablesWithReference := Tstringlist.create;
  lFinishedTables := Tstringlist.create;
  lFinishedTables.Sorted := true;

  try
    // Get All Tables
    FileDestConnection.GetTableNames('', '', '', lTables, [osMy], [tkTable]);

    for lTablename in lTables do
    begin
      // CwLogSave.send(lTablename);
      metaQuery.MetaInfoKind := mkForeignKeys;
      metaQuery.ObjectName := lTablename;
      metaQuery.Open;
      if metaQuery.RecordCount > 0 then
      begin
        lTablesDummy := Tstringlist.create;
        lTablesDummy.Sorted := true;
        lTables.Duplicates := dupIgnore;

        while not metaQuery.eof do
        begin
          lfkey := metaQuery.FieldValues['PKEY_TABLE_NAME'];
          if lfkey <> lTablename then
            lTablesDummy.add(lfkey);
          metaQuery.Next;
        end;
        lTablesWithReference.AddObject(lTablename, lTablesDummy);
      end
      else
      begin
        // There is no reference so put in in the list
        lTablesnoReference.add(lTablename);
      end;
      metaQuery.Close;
    end;

    // Now try the loop


    // First all Tables without any references
    for lTablename in lTablesnoReference do
    begin
      lFinishedTables.add(lTablename);
      CopyData(lTablename); // Die must Du Dir selber bauen
    end;

    while lTablesWithReference.count > 0 do
    begin
      aindex := getIndexforinsert;
      if aindex > -1 then
      begin
        // CwLogSave.send('Insert now ' + lTablesWithReference[aindex]);
        lFinishedTables.add(lTablesWithReference[aindex]);
        if (lTablesWithReference.Objects[aindex] <> nil) then
          Tstringlist(lTablesWithReference.Objects[aindex]).free;
        lTablename := lTablesWithReference[aindex];
        lTablesWithReference.delete(aindex);

        CopyData(lTablename); // Die must Du Dir selber bauen

      end
      else
        break; // Ther is a error
    end;

    if lTablesWithReference.count > 0 then
    begin
      CwLogSave.senderror('Not all found');

      CwLogSave.entermethod('Tables with references');
      for lTablename in lTablesWithReference do
        CwLogSave.send(lTablename);
      CwLogSave.ExitMethod('Tables with references');
    end;

  finally
    lTables.free;
    lTablesnoReference.free;
    lTablesWithReference.free;
    lFinishedTables.free;
  end;

end;

Fritz

[Kostas]

Hallo Fitz,

danke für die Antwort. Ich suche eine Möglichkeit über die Systemtabellen an die Reihenfolge zu kommen da ich die Abfrage innerhalb einer StorredProc benötige.

Gruß Kostas

[jobo]

Rdb$dependencies

[Kostas]

Hallo Zusammen,

ich habe den Artikel gefunden http://firebird-support.yahoogroups....-of-dependency

und daraus die StoredProc erzeugt. Aus meiner Datenbank hat er alle Tabellen komplett richtig ausgelesen.

Code:

create or alter procedure ORDER_ALLTABLES
as
declare variable COUNTNEWINSERTS integer;
declare variable NEWINSERTS integer;
begin
 /* http://firebird-support.yahoogroups.narkive.com/XPYLG1la/odp-firebird-support-listing-table-of-database-in-order-of-dependency */
  delete from tables_ordered;

  INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME)
          SELECT RDB$RELATION_NAME FROM RDB$RELATIONS r
          WHERE RDB$SYSTEM_FLAG=0
          AND NOT EXISTS(SELECT *
                           FROM RDB$INDICES i
                          WHERE r.RDB$RELATION_NAME=i.RDB$RELATION_NAME
                            AND i.RDB$FOREIGN_KEY IS NOT NULL);

  select count(*)
  from tables_ordered
  into :countNewInserts;
  newInserts = 0;

  while (1=1) do
  begin
      INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME)
              SELECT RDB$RELATION_NAME
                FROM RDB$RELATIONS r
               WHERE RDB$SYSTEM_FLAG=0
               AND NOT EXISTS(SELECT *
                                FROM TABLES_ORDERED o /*Ignore tables already inserted*/
                               WHERE r.RDB$RELATION_NAME = o.RDB$RELATION_NAME)
                               AND NOT EXISTS(SELECT *
                                                FROM RDB$INDICES i /*Only insert tables whose foreign key tables are inserted already*/
                                                JOIN RDB$INDICES i2 ON i.RDB$FOREIGN_KEY = i2.RDB$INDEX_NAME
                                                                       AND r.RDB$RELATION_NAME <> i2.RDB$RELATION_NAME /*Omit this line if you don't want to include tables pointing to themselves*/
                                                LEFT JOIN TABLES_ORDERED o ON i2.RDB$RELATION_NAME = o.RDB$RELATION_NAME
                                                WHERE r.RDB$RELATION_NAME=i.RDB$RELATION_NAME
                                                  AND i.RDB$FOREIGN_KEY IS NOT NULL
                                                  AND o.ID IS NULL);

      select count(*)
      from tables_ordered
      into :newInserts;

      if (countNewInserts <> newInserts)then
      begin
        countNewInserts = newInserts;
      end else break;
  end


        
end

Schöne Grüße
Kostas

[Hansa]

Zitat:

Zitat von Kostas (Beitrag 1339906)

wenn eine Firebird Datenbank ForeignKeys verwendet, muss bei Inserts auf die richtige Reihenfolge geachtet werden.

Wo steht das ? Oder wer sagt das ? Reihenfolge von was überhaupt ? Wegen mehrerer Antworter gehe ich da mal davon aus, dass mir jemand eine plausible Antwort geben kann.

[Neutral General]

Zitat:

Zitat von Hansa (Beitrag 1340050)

Zitat:

Zitat von Kostas (Beitrag 1339906)

wenn eine Firebird Datenbank ForeignKeys verwendet, muss bei Inserts auf die richtige Reihenfolge geachtet werden.

Wo steht das ? Oder wer sagt das ? Reihenfolge von was überhaupt ? Wegen mehrerer Antworter gehe ich da mal davon aus, dass mir jemand eine plausible Antwort geben kann.

Du kannst nicht die Lieferadresse zu einem Beleg speichern bevor der Beleg gespeichert ist.
Das meint er.

[Hansa]

Das ist ja wohl klar, aber woher soll die Datenbank bzw. die Systemtabellen das wissen ? Kenne so etwas nur von schlecht programmierten Programmen. Insofern ist die Nachfrage für mich dann doch eher irrelevant.

[Fritzew]

Wenn Foreign Keys da sind muss die Reihenfolge stimmen. Ansonsten tritt ein Fehler auf und das ist ja wohl mehr als korrekt

[Kostas]

Hallo Hansa,

ich verwende sehr intensiv referenzielle Integrität. Tabellen haben Abhängigkeiten (Foreign Keys) Als Beispiel die Tabelle BelegPos referenziert auf die Tabelle Belege. Ich kann also
nicht Belege löschen und dabei die BelegPositionen stehen lassen. Das Bedeutet auch, wenn ich Datensätze in die Datenbank inserte, muss ich zuerst die Datensätze der Tabelle Belege inserten
erst danach kann ich die Datensätze für BelePos inserten. Wenn das Datenbankschema wächst mit mehreren hundert Tabellen wird es unübersichtlich und gefährlich selbst die Tabellenreihenfolge zu
setzen.
Im meinem Fall benötige ich die Funktion für ein automatisierten Restore aus einem Backup der Datenbank.
Mein Anwender macht also ein Backup jeden Tag. Solange nichts passiert, ist alles gut. Wenn jedoch in der Datenbank durch User Fehler Daten gelöscht werden, soll es möglich sein seine aktuelle Datenbank so zu belassen wie sie ist und aus dem Backup die Fehlenden Datensätze zu rekonstruieren. Dabei ist es eben wichtig in der richtige Reihenfolge zu inserten wegen Foreign Key.

Die Funktionalität in einer StoredProc zu haben ist sehr interessant. Ich habe die StoredProc jetzt mit mehreren Datenbanken getestet und sie funktioniert einwandfrei. Was noch zu testen ist, wenn zirkulare Referenzen(Eine Tabelle referenziert sich selbst durch Foreign Key) verwendet werden.

Schöne Grüße Kostas

[Hansa]

Zitat:

Zitat von Kostas (Beitrag 1340058)

Tabellen haben Abhängigkeiten (Foreign Keys) Als Beispiel die Tabelle BelegPos referenziert auf die Tabelle Belege. Ich kann also
nicht Belege löschen und dabei die BelegPositionen stehen lassen.

Das macht ja sowieso die Datenbank von selbst, sofern richtig angelegt. Und das heisst : on delete cascade Du musst der DB sagen, dass es beim Löschen eines Beleges dazu kommen soll, dass auch alle Beleg-Positionen gelöscht werden. Da braucht man gar nichts mehr selber zu machen. Man kann sogar von mir aus 50 verschachtelte Tabellen so behandeln. Und es gibt auch noch on update, on insert. Man kann also da sogar unbesezte NULL-Felder etc. mit sinnvollem Wert besetzen. Wenn ich das hier im Zusammenhang so sehe, dann denke ich, dass das Dir nicht so bekannt ist,

[Kostas]

Hallo Hansa,

:-) das ist mir alles durchaus bekannt. Ich habe eine Ahnung von Datenbank Design.
Was mich jedoch wundert ist, wenn Du eine Ahnung von Datenbank Design hast, warum begreifst du nicht warum ich die Reihenfolge der Tabellen benötige wenn ich erwähne das ich Foreign Keys verwende?
Andere haben es doch auch kapiert!
Sorry, vermutlich habe ich etwas übersehen zu erwähnen.

Also nochmal: Ich verwende bewusst referenzielle Integrität, ich nutze Cascade Delete wo es Sinn macht, ich nutze bei Foreign Keys immer cascade update außer wenn ich es bewusst nicht einsetze.
Bei referenzielle Integrität ist die Reihenfolge der Tabellen bei Inserts elementar wichtig.

Gruß Kostas

[jobo]

Zitat:

Zitat von Kostas (Beitrag 1340042)

Hallo Zusammen,

ich habe den Artikel gefunden http://firebird-support.yahoogroups....-of-dependency

und daraus die StoredProc erzeugt. Aus meiner Datenbank hat er alle Tabellen komplett richtig ausgelesen.

Ich würde mich auf das Script nicht verlassen. Es enthält mehrere Ungereimtheiten.
Die Select Statements darin arbeiten auch mit Views, die da einfach nicht reingehören.
Die Tabelle mit der "Reihenfolge" der Tabellen enthält faktisch keine Reihenfolge, sondern nur Tabellennamen, deren Reihenfolge sowohl beim Befüllen als auch beim späteren Auslesen nicht definiert ist. (Selbst wenn man davon ausgeht, dass die ID Spalte vlt. über einen Generator befüllt wird.)
Außerdem scheint mir die Verwendung der Indexnamen fragwürdig, aber vielleicht ist das ja bei Firebird fest vorgegeben.

[Kostas]

Hallo Jobo,

danke für den Hinweis mit den Views, werde ich noch herausfiltern.

Die Reihenfolge der Tabelle scheint soweit ich es bis jetzt getestet habe, korrekt zu sein.
Die Tabellen werden in der richtigen Reihenfolge abgerufen über das Feld ID

Code:

SELECT RDB$RELATION_NAME
FROM TABLES_ORDERED
ORDER BY ID

Mal sehen was weitere Tests ergeben, aber wie gesagt, bis jetzt stimmt es.
Danke und schöne Grüße.
Kostas

[Lemmy]

Zitat:

Zitat von jobo (Beitrag 1340064)

Die Select Statements darin arbeiten auch mit Views, die da einfach nicht reingehören.

wo werden da views verwendet?

Zitat:

Zitat von jobo (Beitrag 1340064)

Die Tabelle mit der "Reihenfolge" der Tabellen enthält faktisch keine Reihenfolge, sondern nur Tabellennamen, deren Reihenfolge sowohl beim Befüllen als auch beim späteren Auslesen nicht definiert ist. (Selbst wenn man davon ausgeht, dass die ID Spalte vlt. über einen Generator befüllt wird.)

wenn ein Generator verwendet wird, dann sollte die Reihenfolge passen.

Zitat:

Zitat von jobo (Beitrag 1340064)

Außerdem scheint mir die Verwendung der Indexnamen fragwürdig, aber vielleicht ist das ja bei Firebird fest vorgegeben.

Was würdest Du verwenden?

[Kostas]

Die Version ohne Views.

Code:

create or alter procedure ORDER_ALLTABLES
as
declare variable COUNTNEWINSERTS integer;
declare variable NEWINSERTS integer;
begin
 /* http://firebird-support.yahoogroups.narkive.com/XPYLG1la/odp-firebird-support-listing-table-of-database-in-order-of-dependency */
  delete from tables_ordered;

  INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME)
          SELECT RDB$RELATION_NAME FROM RDB$RELATIONS r
          WHERE RDB$SYSTEM_FLAG=0
          AND RDB$RELATION_TYPE = 0 /* nur Tabellen */
          AND NOT EXISTS(SELECT *
                           FROM RDB$INDICES i
                          WHERE r.RDB$RELATION_NAME=i.RDB$RELATION_NAME
                            AND i.RDB$FOREIGN_KEY IS NOT NULL);

  select count(*)
  from tables_ordered
  into :countNewInserts;
  newInserts = 0;

  while (1=1) do
  begin
      INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME)
              SELECT RDB$RELATION_NAME
                FROM RDB$RELATIONS r
               WHERE RDB$SYSTEM_FLAG=0
               AND RDB$RELATION_TYPE = 0 /* nur Tabellen */
               AND NOT EXISTS(SELECT *
                                FROM TABLES_ORDERED o /*Ignore tables already inserted*/
                               WHERE r.RDB$RELATION_NAME = o.RDB$RELATION_NAME)
                               AND NOT EXISTS(SELECT *
                                                FROM RDB$INDICES i /*Only insert tables whose foreign key tables are inserted already*/
                                                JOIN RDB$INDICES i2 ON i.RDB$FOREIGN_KEY = i2.RDB$INDEX_NAME
                                                                       AND r.RDB$RELATION_NAME <> i2.RDB$RELATION_NAME /*Omit this line if you don't want to include tables pointing to themselves*/
                                                LEFT JOIN TABLES_ORDERED o ON i2.RDB$RELATION_NAME = o.RDB$RELATION_NAME
                                                WHERE r.RDB$RELATION_NAME=i.RDB$RELATION_NAME
                                                  AND i.RDB$FOREIGN_KEY IS NOT NULL
                                                  AND o.ID IS NULL);

      select count(*)
      from tables_ordered
      into :newInserts;

      if (countNewInserts <> newInserts)then
      begin
        countNewInserts = newInserts;
      end else break;
  end


        
end

[jobo]

@Lemmy
Die Statements in der SP unterscheiden nicht nach Tabellen und Views. Es werden also Views mit verarbeitet. (im vorigen Post korrigiert)
Der Generator nagelt doch einfach eine ID an die reinkommenden Sätze, aber die reinkommenden Sätze selbst sind ungeordnet. Zumindest sehe ich keinen Sortiermechanismus. Für die Tabellen ohne FK bzw. Index am Anfang ist es natürlich egal. Wie das Ergebnis dann bei Mehrfachabhängigkeiten aussieht, kann ich nicht sagen, ich finde es halt verdächtig.

Ich würde Dependencies nehmen ;)
Hab ich ja oben irgendwo vorgeschlagen, aber das ist offenbar bei Firebird so nicht machbar. (Zumindest sehe ich keine passenden Einträge in meinen Firebird Testdaten)
Ob das nun Datenfehler bei mir sind, oder Firebird ausgerechnet Ref. Constraints nicht als Dependencies behandelt, weiß ich nicht. Ich hab mir auch nicht die Mühe gemacht, Tabellen mit selbstgeschriebenen Indexanweisungen auszuprobbieren. Ist natürlich die Frage, wer sowas macht für RefConstraints, aber mglw. gibt es damit dann auch ein Problem.

[Kostas]

Das mit den Views habe ich schon eingebaut.
Ein Test haben ich durchgeführt mit zirkularer Referenz, das funktioniert auch. Seine eigene Tabelle muss ich noch herausfiltern.
Die Tabellen werden zwar nicht sortiert ausgegeben aber aus Referenz sicht in der richtigen Reihenfolge.
Ich werde es noch nicht produktiv einsetzen aber bis dato konnte ich keinen Fehler feststellen.
Die Tabelle RDB$Dependencies gibt es schon.

[Lemmy]

Zitat:

Zitat von jobo (Beitrag 1340069)

Die Statements in der SP unterscheiden nicht nach Tabellen und Views. Es werden also Views mit verarbeitet.

ahh.. jetzt, dann sag das doch gleich... :-)

Zitat:

Zitat von jobo (Beitrag 1340069)

Der Generator nagelt doch einfach eine ID an die reinkommenden Sätze, aber die reinkommenden Sätze selbst sind ungeordnet. Zumindest sehe ich keinen Sortiermechanismus. Für die Tabellen ohne FK bzw. Index am Anfang ist es natürlich egal. Wie das Ergebnis dann bei Mehrfachabhängigkeiten aussieht, kann ich nicht sagen, ich finde es halt verdächtig.

hm.. zumindest lt. Kommentar werden die Tabellen ausgeschlossen deren ForeignKeys auf Tabellen verweisen die noch nicht in der Liste enthalten sind... [zitat]/*Only insert tables whose foreign key tables are inserted[/zitat]

Zitat:

Zitat von jobo (Beitrag 1340069)

Ich würde Dependencies nehmen ;)
Indexanweisungen auszuprobbieren. Ist natürlich die Frage, wer sowas macht für RefConstraints, aber mglw. gibt es damit dann auch ein Problem.

also zu den INdizes: Die sind eindeutig, d.h. ein entsprechender Index auf dem Namen, sprich da gibt es keine Überschneidungen und sollte daher klappen.

Dependencies gibt es auch (RDB$DEPENDENCIES) - sollte darüber eigentlich auch funktionieren, habe mir die Einträge jetzt aber nicht genauer angeschaut. Aber definitiv eine sinnvolle Sache, da hier ja noch weitere Abhängigkeiten (zu Views, Triggers, SPs) ausgelesen werden können....

Grüße

[Kostas]

Nur der Vollständigkeitshalber,

ich habe zu diesem Thema eine weitere Lösung gefunden die ebenfalls auf indices geht.
Beide StoredProcs gehören zusammen. Die Vorgehensweise ist die gleiche so ich das sehe.

Code:

create or alter procedure DB_TBLS_ROWS_FK (
    INCL_FKS smallint = 1)
returns (
    TBL_NAME varchar(200),
    FKS smallint,
    PK varchar(200),
    TBL_FIELDS bigint,
    TBL_ROWS bigint)
as
declare variable STMT_TO_EXEC varchar(200);
BEGIN
  for
    SELECT p.TBL_NAME, p.FKS
      FROM DB_TBLS_ROWS_FK_0 p order by p.FKS
    into :TBL_NAME, :FKS
  do begin
    TBL_ROWS = null;
    TBL_FIELDS = null;
    STMT_TO_EXEC = 'select count(*) as nr_of_rows from ' || :TBL_NAME;

    execute statement STMT_TO_EXEC INTO :TBL_ROWS;

    select count(*)
      from rdb$relation_fields flds
     where flds.RDB$RELATION_NAME = :TBL_NAME
      into :TBL_FIELDS;
    suspend;

    if(0 <> :INCL_FKS) then
    begin
      for
        SELECT r.RDB$FOREIGN_KEY
          FROM RDB$INDICES r
         WHERE r.RDB$RELATION_NAME= :TBL_NAME AND (r.RDB$FOREIGN_KEY IS NOT NULL)
         into :PK
      do begin
        /*TBL_NAME = null;*/
           FKS = null;
           suspend;
      end
      PK = null;
    end
  end
end

Code:

create or alter procedure DB_TBLS_ROWS_FK_0
returns (
    TBL_NAME varchar(200),
    FKS smallint)
as
BEGIN
  for SELECT DISTINCT RDB$RELATION_NAME
        FROM RDB$RELATION_FIELDS
       WHERE RDB$SYSTEM_FLAG=0
       into :TBL_NAME
  do begin
       SELECT count(RDB$INDEX_NAME)
         FROM RDB$INDICES
        WHERE RDB$RELATION_NAME= :TBL_NAME AND (RDB$FOREIGN_KEY IS NOT NULL)
       into :FKS;

       suspend;
  end
END

[jobo]

Hier noch ein Statement, das vielleicht etwas sauberer ist *, als das in der ersten Proc. Es basiert tatsächlich auf den Dictionary Infos zu Foreign Key Constraints und liefert mehr Infos, als benötigt.
Stammt von hier und ist etwas modifiziert:
http://www.alberton.info/firebird_sql_meta_info.html
Dort gibt es auch eine Variante, die auf rdb$depencies basiert, aber die geht bei mir gar nicht.

Ich finde es ja seltsam, dass diese Daten offenbar nur über die Indexinfos erreichbar sind. Das hat m.E. logisch nichts miteinander zu tun. Aber da die Indexerzeugung bei ForeignKey Erzeugunng ebenfalls automatisch läuft (und dabei auch bestehende Indices ignoriert, also zusätzlich/doppelt anlegt), muss man das wohl als pragmatischen Ansatz sehen.

Code:

   SELECT rc.RDB$RELATION_NAME as Constraint_Tablename, 
          rc.RDB$CONSTRAINT_NAME,
          s.RDB$FIELD_NAME AS field_name,
          rc.RDB$CONSTRAINT_TYPE AS constraint_type,
          refc.RDB$UPDATE_RULE AS on_update,
          refc.RDB$DELETE_RULE AS on_delete,
          refc.RDB$MATCH_OPTION AS match_type,
          i2.RDB$RELATION_NAME AS references_table,
          s2.RDB$FIELD_NAME AS references_field,
          (s.RDB$FIELD_POSITION + 1) AS field_position
     FROM RDB$INDEX_SEGMENTS s 
     LEFT JOIN RDB$RELATION_CONSTRAINTS rc 
       ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
     LEFT JOIN RDB$REF_CONSTRAINTS refc 
       ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
     LEFT JOIN RDB$RELATION_CONSTRAINTS rc2 
       ON rc2.RDB$CONSTRAINT_NAME = refc.RDB$CONST_NAME_UQ
     LEFT JOIN RDB$INDICES i2 
       ON i2.RDB$INDEX_NAME = rc2.RDB$INDEX_NAME
     LEFT JOIN RDB$INDEX_SEGMENTS s2 
       ON i2.RDB$INDEX_NAME = s2.RDB$INDEX_NAME AND s.RDB$FIELD_POSITION = s2.RDB$FIELD_POSITION
    WHERE -- rc.RDB$RELATION_NAME='TESTTABLE'       -- table name
          -- AND rc.RDB$CONSTRAINT_NAME='FK_B'      -- constraint name
          -- AND rc.RDB$CONSTRAINT_TYPE IS NOT NULL 
          rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
    ORDER BY s.RDB$FIELD_POSITION

*
Einen Schönheitspreis gibt's bei dem Thema offenbar nicht zu gewinnen.

[Kostas]

Hallo Jobo,

dieser Select alleine liefert leider nicht die richtige Reihenfolge, zudem auch die Tabellen mehrfach wenn sie mehrere Foreign Key haben.
Ich habe auch soeben versucht in WHERE rc.RDB$RELATION_NAME den Tabellenname zu übergeben, leider auch ohne Erfolg.

Code:

SELECT rc.RDB$RELATION_NAME as Constraint_Tablename, 
          rc.RDB$CONSTRAINT_NAME,
          s.RDB$FIELD_NAME AS field_name,
          rc.RDB$CONSTRAINT_TYPE AS constraint_type,
          refc.RDB$UPDATE_RULE AS on_update,
          refc.RDB$DELETE_RULE AS on_delete,
          refc.RDB$MATCH_OPTION AS match_type,
          i2.RDB$RELATION_NAME AS references_table,
          s2.RDB$FIELD_NAME AS references_field,
          (s.RDB$FIELD_POSITION + 1) AS field_position
     FROM RDB$INDEX_SEGMENTS s 
     LEFT JOIN RDB$RELATION_CONSTRAINTS rc 
       ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
     LEFT JOIN RDB$REF_CONSTRAINTS refc 
       ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
     LEFT JOIN RDB$RELATION_CONSTRAINTS rc2 
       ON rc2.RDB$CONSTRAINT_NAME = refc.RDB$CONST_NAME_UQ
     LEFT JOIN RDB$INDICES i2 
       ON i2.RDB$INDEX_NAME = rc2.RDB$INDEX_NAME
     LEFT JOIN RDB$INDEX_SEGMENTS s2 
       ON i2.RDB$INDEX_NAME = s2.RDB$INDEX_NAME AND s.RDB$FIELD_POSITION = s2.RDB$FIELD_POSITION
    WHERE -- rc.RDB$RELATION_NAME='TESTTABLE'      -- table name
          -- AND rc.RDB$CONSTRAINT_NAME='FK_B'     -- constraint name
          -- AND rc.RDB$CONSTRAINT_TYPE IS NOT NULL 
          rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'

      and    rc.RDB$RELATION_NAME in (SELECT RDB$RELATION_NAME
                                     FROM RDB$RELATIONS r
                                    WHERE RDB$SYSTEM_FLAG=0
                                      AND RDB$RELATION_TYPE = 0 /* nur Tabellen */
                                      AND RDB$RELATION_NAME <> 'TABLES_ORDERED')



    ORDER BY s.RDB$FIELD_POSITION

Gruß Kostas

[jobo]

Zitat:

Zitat von Kostas (Beitrag 1340191)

dieser Select alleine liefert leider nicht die richtige Reihenfolge, zudem auch die Tabellen mehrfach wenn sie mehrere Foreign Key haben.
Ich habe auch soeben versucht in WHERE rc.RDB$RELATION_NAME den Tabellenname zu übergeben, leider auch ohne Erfolg.

Sorry, ich hätte vielleicht mehr dazu schreiben sollen. Das Statement ist zum "Spielen" gedacht, es liefert nicht primär einfach nur die Ziel-Tabellen. Es soll bspw. bei den Constraints mehrere Spalten berücksichtigen-was bei Deinem Problem erstmal nicht hilft, wie Du festgestellt hast- usw. usw.

Wenn es richtig aufbereitet ist, wäre es Ersatz für das 2. Insert/Select Statement aus der SP von Dir.

Ich finde das spannend, aber ich hab leider kein Material/ Zeit, das zu testen. Deine SP hat z.B. bei meinem "Datenbestand" ursprünglich nicht funktioniert. Erst nachdem ich einige Male mit Flamerobin Constraints entfernt und hinzugefügt hatte, wurden die abhängigen Tabellen richtig ausgegeben.

P.S. Die Einschränkung, die Du angehängt hast, sollte eigentlich unnötig sein (bis auf die "ordered" Tabelle selbst), da per se nur Tabellen rauskommen können, die einen definierten Ref.Constraint führen. Views fallen automatisch raus. Das z.B. meinte ich mit "sauberer". Falls bei Dir nicht das richtige angezeigt wird, hat Dein Dictionary vielleicht das gleiche Problem wie meins und ist irgendwie durcheinander.