feat: reverse foreign keys on "Foreign keys" tab in table editor

ansgarbecker · ansgarbecker · commit ca483435a6ad · 2026-02-28T14:09:41.000+01:00
Refs #1825
diff --git a/source/dbstructures.mysql.pas b/source/dbstructures.mysql.pas
@@ -3340,6 +3340,12 @@ function TMySqlProvider.GetSql(AId: TQueryId): string;
       'SHOW TABLE STATUS LIKE :EscapedName',
       ''
       );
+    qGetReverseForeignKeys: Result := 'SELECT DISTINCT'+
+      ' k.TABLE_SCHEMA, k.TABLE_NAME'+
+      ' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k'+
+      ' WHERE'+
+      '   REFERENCED_TABLE_SCHEMA = :EscapedDatabase AND'+
+      '   REFERENCED_TABLE_NAME = :EscapedName';
     else Result := inherited;
   end;
 end;
diff --git a/source/dbstructures.pas b/source/dbstructures.pas
@@ -45,7 +45,8 @@ interface
     qFuncLength, qFuncCeil, qFuncLeft, qFuncNow, qFuncLastAutoIncNumber,
     qLockedTables, qDisableForeignKeyChecks, qEnableForeignKeyChecks,
     qOrderAsc, qOrderDesc, qGetRowCountExact, qGetRowCountApprox,
-    qForeignKeyDrop, qGetTableColumns, qGetCollations, qGetCollationsExtended, qGetCharsets);
+    qForeignKeyDrop, qGetTableColumns, qGetCollations, qGetCollationsExtended, qGetCharsets,
+    qGetReverseForeignKeys);
   TSqlProvider = class
     strict protected
       FNetType: TNetType;
diff --git a/source/table_editor.lfm b/source/table_editor.lfm
@@ -26,7 +26,6 @@ object frmTableEditor: TfrmTableEditor
     Width = 700
     ActivePage = tabBasic
     Align = alTop
-    AutoSize = True
     Images = MainForm.ImageListMain
     TabIndex = 0
     TabOrder = 0
@@ -77,7 +76,6 @@ object frmTableEditor: TfrmTableEditor
         AnchorSideTop.Side = asrBottom
         AnchorSideRight.Control = tabBasic
         AnchorSideRight.Side = asrBottom
-        AnchorSideBottom.Control = tabBasic
         AnchorSideBottom.Side = asrBottom
         Left = 96
         Height = 104
@@ -456,15 +454,21 @@ object frmTableEditor: TfrmTableEditor
       ClientWidth = 692
       ImageIndex = 136
       object tlbForeignKeys: TToolBar
+        AnchorSideLeft.Control = tabForeignKeys
+        AnchorSideTop.Control = tabForeignKeys
+        AnchorSideBottom.Control = tabForeignKeys
+        AnchorSideBottom.Side = asrBottom
         Left = 0
         Height = 142
         Top = 0
         Width = 67
-        Align = alLeft
+        Align = alNone
+        Anchors = [akTop, akLeft, akBottom]
         AutoSize = True
         ButtonHeight = 28
         ButtonWidth = 66
         Caption = 'tlbForeignKeys'
+        Constraints.MaxWidth = 100
         Images = MainForm.ImageListMain
         List = True
         ShowCaptions = True
@@ -496,11 +500,17 @@ object frmTableEditor: TfrmTableEditor
         end
       end
       object listForeignKeys: TLazVirtualStringTree
+        AnchorSideLeft.Control = tlbForeignKeys
+        AnchorSideLeft.Side = asrBottom
+        AnchorSideTop.Control = tabForeignKeys
+        AnchorSideRight.Control = spltForeignKeyListings
+        AnchorSideBottom.Control = tabForeignKeys
+        AnchorSideBottom.Side = asrBottom
         Left = 67
         Height = 142
         Top = 0
-        Width = 625
-        Align = alClient
+        Width = 439
+        Anchors = [akTop, akLeft, akRight, akBottom]
         EditDelay = 0
         Header.AutoSizeIndex = -1
         Header.Columns = <        
@@ -538,7 +548,7 @@ object frmTableEditor: TfrmTableEditor
             Options = [coDraggable, coEnabled, coParentBidiMode, coParentColor, coResizable, coShowDropMark, coVisible, coAllowFocus]
             Position = 5
             Text = 'On DELETE'
-            Width = 85
+            Width = 10
           end>
         Header.Options = [hoAutoResize, hoColumnResize, hoDrag, hoShowSortGlyphs, hoVisible, hoDisableAnimatedResize]
         Header.PopupMenu = MainForm.popupListHeader
@@ -558,6 +568,50 @@ object frmTableEditor: TfrmTableEditor
         OnNewText = listForeignKeysNewText
         OnStructureChange = AnyTreeStructureChange
       end
+      object ListViewReverseForeignKeys: TListView
+        AnchorSideLeft.Control = spltForeignKeyListings
+        AnchorSideLeft.Side = asrBottom
+        AnchorSideTop.Control = tabForeignKeys
+        AnchorSideRight.Control = tabForeignKeys
+        AnchorSideRight.Side = asrBottom
+        AnchorSideBottom.Control = tabForeignKeys
+        AnchorSideBottom.Side = asrBottom
+        Left = 514
+        Height = 142
+        Top = 0
+        Width = 178
+        Anchors = [akTop, akLeft, akRight, akBottom]
+        Columns = <        
+          item
+            AutoSize = True
+            Caption = 'Database'
+            Width = 63
+          end        
+          item
+            AutoSize = True
+            Caption = 'Table'
+            Width = 44
+          end>
+        ColumnClick = False
+        ReadOnly = True
+        RowSelect = True
+        SmallImages = MainForm.ImageListMain
+        TabOrder = 2
+        ViewStyle = vsReport
+      end
+      object spltForeignKeyListings: TSplitter
+        AnchorSideTop.Control = tabForeignKeys
+        AnchorSideRight.Control = ListViewReverseForeignKeys
+        AnchorSideBottom.Control = tabForeignKeys
+        AnchorSideBottom.Side = asrBottom
+        Left = 506
+        Height = 142
+        Top = 0
+        Width = 8
+        Align = alNone
+        Anchors = [akTop, akBottom]
+        ResizeAnchor = akRight
+      end
     end
     object tabCheckConstraints: TTabSheet
       Caption = 'Check constraints'
diff --git a/source/table_editor.pas b/source/table_editor.pas
@@ -21,7 +21,9 @@   TfrmTableEditor = class(TFrame)
     btnDiscard: TButton;
     btnHelp: TButton;
     listColumns: TLazVirtualStringTree;
+    ListViewReverseForeignKeys: TListView;
     PageControlMain: TPageControl;
+    spltForeignKeyListings: TSplitter;
     tabBasic: TTabSheet;
     tabIndexes: TTabSheet;
     tabOptions: TTabSheet;
@@ -213,6 +215,7 @@   TfrmTableEditor = class(TFrame)
     { Private declarations }
     FLoaded: Boolean;
     CreateCodeValid, AlterCodeValid: Boolean;
+    FReverseForeignKeysLoaded: Boolean;
     FColumns: TTableColumnList;
     FKeys, FDeletedKeys: TTableKeyList;
     FForeignKeys: TForeignKeyList;
@@ -248,6 +251,7 @@   TfrmTableEditor = class(TFrame)
     procedure CalcMinColWidth;
     procedure UpdateTabCaptions;
     function MoveNodeAllowed(Sender: TLazVirtualStringTree): Boolean;
+    procedure LoadReverseForeignKeys(Sender: TObject);
   public
     { Public declarations }
     constructor Create(AOwner: TComponent); override;
@@ -448,6 +452,7 @@ procedure TfrmTableEditor.Init(Obj: TDBObject);
   ResetModificationFlags;
   CreateCodeValid := False;
   AlterCodeValid := False;
+  FReverseForeignKeysLoaded := False;
   PageControlMainChange(Self); // Foreign key editor needs a hit
   // Buttons are randomly moved, since VirtualTree update, see #440
   btnSave.Top := Height - btnSave.Height - 3;
@@ -2556,7 +2561,10 @@ procedure TfrmTableEditor.PageControlMainChange(Sender: TObject);
   listForeignKeys.EndEditNode;
   listCheckConstraints.EndEditNode;
   // Ensure SynMemo's have focus, otherwise Select-All and Copy actions may fail
-  if PageControlMain.ActivePage = tabCREATEcode then begin
+  if PageControlMain.ActivePage = tabForeignKeys then begin
+    LoadReverseForeignKeys(Sender);
+  end
+  else if PageControlMain.ActivePage = tabCREATEcode then begin
     SynMemoCreateCode.TrySetFocus;
   end
   else if PageControlMain.ActivePage = tabALTERcode then begin
@@ -3070,6 +3078,34 @@ procedure TfrmTableEditor.listForeignKeysNewText(Sender: TBaseVirtualTree;
   end;
 end;
 
+procedure TfrmTableEditor.LoadReverseForeignKeys(Sender: TObject);
+var
+  SqlGet: String;
+  Results: TDBQuery;
+  ListItem: TListItem;
+begin
+  if FReverseForeignKeysLoaded then
+    Exit;
+  SqlGet := DBObject.Connection.SqlProvider.GetSql(qGetReverseForeignKeys, DBObject.AsStringMap);
+  if SqlGet.IsEmpty then
+    Exit;
+  ListViewReverseForeignKeys.BeginUpdate;
+  ListViewReverseForeignKeys.Clear;
+  try
+    Results := DBObject.Connection.GetResults(SqlGet);
+    while not Results.Eof do begin
+      ListItem := ListViewReverseForeignKeys.Items.Add;
+      ListItem.ImageIndex := ICONINDEX_TABLE;
+      ListItem.Caption := Results.Col(0);
+      ListItem.SubItems.Add(Results.Col(1));
+      Results.Next;
+    end;
+  except
+    on EDbError do;
+  end;
+  ListViewReverseForeignKeys.EndUpdate;
+  FReverseForeignKeysLoaded := True;
+end;
 
 procedure TfrmTableEditor.btnHelpClick(Sender: TObject);
 begin
