violation of FOREIGN KEY constraint

[Zwirbel]

Hallo,

um Daten aus unserer alten "Datenbank" (Paradox) nach FB3 zu heben habe ich eine kleine Delphi Applikation geschrieben. Dabei stellte ich fest, dass ich in einigen Detailtabellen Datensätze enthalten habe, wozu gar keine entsprechende Datensätze in der dazugehörigen Mastertabelle vorhanden sind. Also Karteileichen, wenn man so will. Das führt dann aber dazu, dass ich bei der Migration Fehler erhalte.

Delphi-Quellcode:

FireDac Fehler
The application performed an incorrect operation with the database.
-------------------------------------------
Message text: violation of FOREIGN KEY constraint "FK_TA_BagStatCal" on table "TA_BagStatCal"
Foreign key reference target does not exist
Problematic key value is ("BagStatListId" = 0)
Error code: 335544466
Error kind: FKViolated
Server object: 
Command text offset: 
-------------------------------------------
Command text: INSERT INTO "TA_BagStatCal" ("BagStatCalId", "BagStatCalListId", "ReferenceDate", "BagStatListId", "DayToUse", "Memo", "Created", "Modified", "CreatedBy", "ModifiedBy", "UserId", "State", "StatusId") VALUES (:BagStatCalId, :BagStatCalListId, :ReferenceDate, :BagStatListId, :DayToUse, :Memo, :Created, :Modified, :CreatedBy, :ModifiedBy, :UserId, :State, :StatusId)

Command parameters:
  BAGSTATCALID=8767
  BAGSTATCALLISTID=2
  REFERENCEDATE=01.01.2007
  BAGSTATLISTID=0
  DAYTOUSE=1
  MEMO=
  CREATED=22.07.2014 10:40:15
  MODIFIED=22.07.2014 10:40:15
  CREATEDBY=1
  MODIFIEDBY=1
  USERID=1
  STATE=22.07.2014 10:40:15
  STATUSID=0
  BagStatCalId=<null>
  BagStatCalListId=<null>
  ReferenceDate=<null>
  BagStatListId=<null>
  DayToUse=<null>
  Memo=<null>
  Created=<null>
  Modified=<null>
  CreatedBy=<null>
  ModifiedBy=<null>
  UserId=<null>
  State=<null>
  StatusId=<null>
-------------------------------------------
Exception class name: EIBNativeException
FireDAC error code: 1400
FireDAC object name: frmAMU.qry_FB

Meine Lösung sieht nun derzeit so aus, vor der Migration alle Quelldaten die in einer Master/Detailbeziehung stehen aufzubereiten und Detaildatensätze ohne passenden Masterdatensatz in den Originaldaten löschen. Aber das dauert alles extrem lange. Bei den meisten Kunden würde das keine große Rolle spielen, ob der einmalige Migrationsvorgang nun 10 Minuten oder 1 Stunde dauert. Aber in einigen Situationen sind 24/7-Systeme zu migrieren, da kommt es auf jede Minute an.

Delphi-Quellcode:

procedure TfrmAMU.CleanupTable_BagStatCal(const aTA_BagStatListAvailable : Boolean);
  { Die Detailtabelle BagStatCal kann Datensätze enthalten die keine Referenz zur
    Mastertabelle BagStatList haben. Diese verweisten Datensätze sind zu
    löschen. »aTA_BagStatListAvailable« gibt an, ob die Tabelle "BagStatList"
    auch existiert. Wenn sie nicht existiert, dann werden die Daten in "BagStatCal"
    alle gelöscht. }
var
  StartTime,
  StopTime         : TDateTime;
  Statement        : string;
  Id, i            : Integer;
  DoDelete         : Boolean;
begin
  StartLog(StartTime, 'CleanupTable_BagStatCal');
  try
    try
      { Liste alle BagStatListId-Datensätze in der Paradoxtabelle
        "BagStatCal" auf die es in der Detailtabelle gibt. }
      Statement := 'SELECT BagStatListId FROM BagStatCal GROUP BY BagStatListId';
      DoLog(Statement);
      qry_Pdx_Repair_Select.SQL.Clear;
      qry_Pdx_Repair_Select.SQL.Add(Statement);
      qry_Pdx_Repair_Select.Open;
      qry_Pdx_Repair_Select.First;
      for i := 0 to Pred(qry_Pdx_Repair_Select.RecordCount) do begin
        if aTA_BagStatListAvailable then begin
          { Schauen ob es zu jedem BagStatListId-Datensatz in der Mastertabelle
            auch Datensätze gibt. }
          Id := qry_Pdx_Repair_Select.FieldByName('BagStatListId').AsInteger;
          Statement := Format('SELECT * FROM BagStatList WHERE BagStatListId = %d', [Id]);
//          DoLog(Statement);
          qry_Pdx_Repair_Delete.SQL.Clear;
          qry_Pdx_Repair_Delete.SQL.Add(Statement);
          qry_Pdx_Repair_Delete.Open;
          DoDelete := qry_Pdx_Repair_Delete.RecordCount = 0;
          qry_Pdx_Repair_Delete.Close;
        end else begin
          Id := qry_Pdx_Repair_Select.FieldByName('BagStatListId').AsInteger;
          DoDelete := True;
        end;

        if (i mod 5) = 0 then
          MWApplicationProcessMessages;

        if FStoped then
          Break;

        { Es hat Leichen. Diese Detaildatensätze löschen. }
        if DoDelete then begin
          Statement := Format('DELETE FROM BagStatCal WHERE BagStatListId = %d', [Id]);
          DoLog(Format('Delete orphans. %s', [Statement]));
          qry_Pdx_Repair_Delete.SQL.Clear;
          qry_Pdx_Repair_Delete.SQL.Add(Statement);
          qry_Pdx_Repair_Delete.ExecSQL;
        end;
        qry_Pdx_Repair_Select.Next;
      end;
    finally
      qry_Pdx_Repair_Select.Close;
    end;
    StopLog(StartTime, StopTime, 'CleanupTable_BagStatCal');
  except
    on E : Exception do begin
      HandleExceptionPrim(E, E.message, 1001, 'CleanupTable_BagStatCal');
      raise;
    end;
  end;
end;

Es ist sicherlich zu bedenken, dass mit ausgetüftelten SQL-Statements auf den Original-Paradox-Daten kein Blumentopf zu gewinnen ist.

Die beiden Tabellen die zum Fehler oben passen sehen so aus:

Delphi-Quellcode:

/* Mastertabelle +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */
CREATE TABLE "TA_BagStatList" (
  "BagStatListId"              "DO_UniqueId",
  "ShortDesc"                  "DO_ShortDesc" NOT NULL,
  "LongDesc"                   "DO_LongDesc",
  "OPS_DayBased"               SMALLINT,
  "NrOfDays"                   SMALLINT,
  "Memo"                       "DO_Memo",
  "Created"                    "DO_Created",
  "Modified"                   "DO_Modified",
  "CreatedBy"                  "DO_CreatedBy",
  "ModifiedBy"                 "DO_ModifiedBy",
  "UserId"                     "DO_UserId",
  "State"                      "DO_State",
  "StatusId"                   "DO_StatusId"
);

ALTER TABLE "TA_BagStatList" ADD CONSTRAINT "PK_BagStatList" PRIMARY KEY ("BagStatListId");
CREATE SEQUENCE "SE_TA_BagStatList_BagStatListId" START WITH 0 INCREMENT BY 1; ALTER SEQUENCE "SE_TA_BagStatList_BagStatListId" RESTART WITH 0;

/* Detailtabelle +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */
CREATE TABLE "TA_BagStatCal" (
  "BagStatCalId"               "DO_UniqueId",
  "BagStatCalListId"           "DO_UniqueId",
  "ReferenceDate"              "DO_TimeStamp" NOT NULL,
  "BagStatListId"              "DO_UniqueId",
  "DayToUse"                   SMALLINT,
  "Memo"                       "DO_Memo",
  "Created"                    "DO_Created",
  "Modified"                   "DO_Modified",
  "CreatedBy"                  "DO_CreatedBy",
  "ModifiedBy"                 "DO_ModifiedBy",
  "UserId"                     "DO_UserId",
  "State"                      "DO_State",
  "StatusId"                   "DO_StatusId"
);

ALTER TABLE "TA_BagStatCal" ADD CONSTRAINT "PK_BagStatCal" PRIMARY KEY ("BagStatCalId");
CREATE INDEX "IF_TA_BagStatCal_ReferenceDate" ON "TA_BagStatCal" "BagStatCalListId","ReferenceDate";
ALTER TABLE "TA_BagStatCal"
  ADD CONSTRAINT "FK_TA_BagStatCal"
  FOREIGN KEY ("BagStatListId")
  REFERENCES "TA_BagStatList"("BagStatListId")
  ON DELETE CASCADE; /* löscht alle Datensätze in der Detailtabelle "TA_BagStatCal", wenn der Masterdatensatz in "TA_BagStatList" gelöscht wird */

CREATE SEQUENCE "SE_TA_BagStatCal_BagStatCalId" START WITH 0 INCREMENT BY 1; ALTER SEQUENCE "SE_TA_BagStatCal_BagStatCalId" RESTART WITH 0;

/* Trigger für eine eindeutige Id */
SET TERM ^;
CREATE OR ALTER TRIGGER "TR_TA_BagStatCal_BI" FOR "TA_BagStatCal" ACTIVE BEFORE INSERT
AS BEGIN
  IF(NEW."BagStatCalId" IS NULL) THEN NEW."BagStatCalId" = GEN_ID("SE_TA_BagStatCal_BagStatCalId",1);
END ^

Hat jemand eine Idee wie man das lösen kann, ohne die Originaldaten zu bereinigen? Also quasi beim Einfügen der Daten dafür zu sorgen, dass ein Detaildatensatz nicht eingefügt wird. Derzeit füge ich die Daten während der Migration per INSERT ein:

Delphi-Quellcode:

            InsertSQL := Format('INSERT INTO "%s" (%s) VALUES (%s)', [TableName, InsertFieldNames, InsertFieldValues]);
            qry_FB.SQL.Add(InsertSQL);
...
                { Nun die Werte je nach Datentyp übertragen von der Quelle ins Ziel }
                case tbl_Pdx.Fields[k].DataType of
                  ftString           :
                    qry_FB.ParamByName(FieldNameUpper).AsString := tbl_Pdx.FieldByName(FieldNameMixed).AsString;
                  ftSmallint,
                  ftInteger,
                  ftWord,
                  ftAutoInc          :
                    qry_FB.ParamByName(FieldNameUpper).AsInteger := tbl_Pdx.FieldByName(FieldNameMixed).AsInteger;
...
                qry_FB.ExecSQL(InsertSQL);

Vielen Dank im Voraus. Gruß, Markus

[DeddyH]

Kannst Du nicht ganz einfach den FK zunächst weglassen, die Daten komplett einlesen, anschließend in der neuen DB alle Detaildatensätze ohne entsprechenden Master löschen und danach erst den FK definieren?

[Jumpy]

Keine Ahnung ob das mit Firebird geht, aber ich würde einfach die FK-Constrains (vorübergehend) deaktivieren, alle Daten in die Firebird-DB schießen und diese dann dort mit entsprechenden SQL-Statements schnell(?) bereinigen. Anschließend, wenn alles sauber ist, die Constraints wieder aktivieren.

Edit: 2 Dumme ein Gedanke, aber keine rote Box .

[Zwirbel]

Zitat von Jumpy:

Keine Ahnung ob das mit Firebird geht, aber ich würde einfach die FK-Constrains (vorübergehend) deaktivieren, alle Daten in die Firebird-DB schießen und diese dann dort mit entsprechenden SQL-Statements schnell(?) bereinigen. Anschließend, wenn alles sauber ist, die Constraints wieder aktivieren.

Dazu muss ich mir anschauen, wie ich das regel, bestimmte contrains erst hinterher einzuschalten, derzeit habe ich es so geregelt, dass das Migrationsprogramm die gesamte Datenbank per SQL-Skript über IBExpert erzeugt. Dann muss ich den Part wohl splitten, erst mal nur die Tabellen anlegen, Daten reinpumpen und hinterher die Contraints anlegen, hat vllt. allgemein den Vorteil, dass das schneller geht.

Die spannendere Frage ist, wie würde denn ein solches SQL-Statement zum Bereinigen aussehen? Da ich wie gesagt von SQL noch nicht arg viel verstehe, kann mir da jemand möglichst etwas beispielhaftes zeigen? Danke, Gruß, Markus

[Jumpy]

Zitat von Zwirbel:

Die spannendere Frage ist, wie würde denn ein solches SQL-Statement zum Bereinigen aussehen? Da ich wie gesagt von SQL noch nicht arg viel verstehe, kann mir da jemand möglichst etwas beispielhaftes zeigen? Danke, Gruß, Markus

Konkret müsste man dann im Einzelfall schauen, aber so ungefähr:

SQL-Code:

--Wenn man sich erstmal die betroffenen Datensätze angucken will
Select * From Detail_Tabelle
Where FK_Feld not in 
(Select PK_Feld From Master_Tabelle)

--Wenn man's dann löschen will
Delete From Detail_Tabelle
Where FK_Feld not in 
(Select PK_Feld From Master_Tabelle)

Das wäre jetzt für den Fall, das z.B. im Master sowas wie Aufträge sind, während in Detail die dazugehörigen Auftragspositionen stehen und wo es jetzt Auftragspositionen gibt, zu denen es im Master gar keine Aufträge gibt.

[hoika]

Hallo,
Paradox, die gute alte Zeit

Also ich würde vom SQL die Finger weglassen.
Sowas wie

Delete From DetailTable Where DetailTable.MasterId not In (Select MasterTable.MasterId From MasterTable)
dauert unter Paradox i.d.R. sehr lange.

Wenn du eh an alle Tabellen ranmusst:
Select DetailId,MasterId From DetailTable -> rein in eine TStringList DetailList
Select MasterTable.MasterId From MasterTable -> rein in eine TStringList MasterList

und jetzt alle Details durchlaufen und nach der MasterId in der MasterList suchen,
wenn nicht vorhanden, den Detailsatz löschen.

[nahpets]

Was ich hier aus dem Quelltext nicht ganz so genau entnehmen kann:

Delphi-Quellcode:

            InsertSQL := Format('INSERT INTO "%s" (%s) VALUES (%s)', [TableName, InsertFieldNames, InsertFieldValues]);
            qry_FB.SQL.Add(InsertSQL);
...
                { Nun die Werte je nach Datentyp übertragen von der Quelle ins Ziel }
                case tbl_Pdx.Fields[k].DataType of
                  ftString :
                    qry_FB.ParamByName(FieldNameUpper).AsString := tbl_Pdx.FieldByName(FieldNameMixed).AsString;
                  ftSmallint,
                  ftInteger,
                  ftWord,
                  ftAutoInc :
                    qry_FB.ParamByName(FieldNameUpper).AsInteger := tbl_Pdx.FieldByName(FieldNameMixed).AsInteger;
...
                qry_FB.ExecSQL(InsertSQL);

Werden die Daten satzweise übernommen?

Wenn ja, Methode einfach, billig und unelegant:

Delphi-Quellcode:

Try
  qry_FB.ExecSQL(InsertSQL);
except
  on e : Exception do begin
    // ggfls. Fehlertyp abfragen und Fehlermeldung in 'ne Logdatei schreiben.
  end;
end;

Wie greifts Du auf die Datenbanken zu, noch über die BDE?

Wenn ja, dann schau Dir (soweit in Deinem Delphi vorhanden) bitte einmal die Komponente TBatchMove an.

Da kann man einiges Konfigurieren:

Quelle und Ziel.
Feldmapping.
Verhalten im Fehlerfalle.
Ausgabetabelle für die Datensätze mit Schlüsselverletzungen.

Eventuell etwas Literatur zu der Komponente:

http://docwiki.embarcadero.com/RADSt...wenden_-_Index
http://edn.embarcadero.com/article/25620
http://docs.embarcadero.com/products...epart_xml.html

Ach, was mir noch einfiel:

Wird die BDE benutzt, so kann man in der Localsql.hlp nachlesen, was mit SQL möglich ist. Dazu gehören auch die "handelsüblichen" Joins.

Die Hilfedatei liegt gewöhnlich im gleichen Verzeichnis, wie BDEAdmin.exe.

[Jumpy]

Zitat von hoika:

Hallo,
Also ich würde vom SQL die Finger weglassen.
Sowas wie

Delete From DetailTable Where DetailTable.MasterId not In (Select MasterTable.MasterId From MasterTable)
dauert unter Paradox i.d.R. sehr lange.

Das war jetzt auch eher so gedacht, dass die Daten ohne Constraints, in die neue/modernere DB übertragen werden und da dann das SQL ausgeführt wird, bevor die Constraints dann aktiviert werden.

In Paradox/BDE würde ich sowas auch nicht machen wollen.

[Jasocul]

Moin,
wenn ich das richtig sehe, überträgst du jeden Datensatz einzeln.
Was spricht dagegen, vor der Übertragung zu prüfen, ob in der Mastertabelle überhaupt ein passender Datensatz existiert?
Hat die Mastertabelle keinen passenden Key, überträgst du den Datensatz einfach nicht.

[Zwirbel]

Zitat von Jasocul:

Moin,
wenn ich das richtig sehe, überträgst du jeden Datensatz einzeln.
Was spricht dagegen, vor der Übertragung zu prüfen, ob in der Mastertabelle überhaupt ein passender Datensatz existiert?
Hat die Mastertabelle keinen passenden Key, überträgst du den Datensatz einfach nicht.

Der Gedanke war mir während des Schreibens meiner Frage auch gekommen. Aber diese Einfüge-Routine weiß von Master/Detail nichts, weil sie global für alle Tabellen gleich arbeitet. Da müsste ich dann spezielle Anweisungen schreiben, bei bestimmten Tabellennamen darauf zu achten, ob in einer zweiten (Master-)Tabelle ein passender Datensatz enthalten ist. Ist auf jeden Fall eine Option über die ich vllt. doch noch mal weiter grübeln sollte. Ist vllt. pragmatischer. Danke.