title: Delphi check dataset for foreignkey Mysql
body: <p>I have fucntion to check all foreign key constraint , my database has 500 tables , fields with many fk contraints take long time ( 10 sec ):
  I use this fucntion in beforepost of dataset like this:</p>
  
  <pre><code>  msg := check_foreign_key(Dataset,'employee',field_name);
    if msg &lt;&gt; '' then
    begin
      ShowMessage(msg);
      abort;
      Exit;
    end;
  </code></pre>
  
  <p>how can i reduce this time, this is my function:</p>
  
  <pre><code>function check_foreign_key(ds: TDataSet;table_name: string;var ret_field_name: string): string;
  var
    qry1,qry2: TUniQuery;
    ref_table:String;
  begin
    Result := '';
    qry1 := TUniQuery.Create(nil);
    qry2 := TUniQuery.Create(nil);
    try
      qry1.Connection := my_db;
      with qry1 do
      begin
        close;
        SQL.Clear;
        SQL.Add('select COLUMN_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME');
        SQL.Add('from INFORMATION_SCHEMA.KEY_COLUMN_USAGE');
        SQL.Add('where TABLE_SCHEMA = ''' + my_db.Database + ''' and TABLE_NAME = ''' + table_name + '''');
        SQL.Add('and referenced_column_name is not NULL');
        Open;
      end;
      qry2.Connection := my_db;;
      while not qry1.Eof do
      begin
        if not DataSet_Has_Field(ds,qry1.FieldByName('COLUMN_NAME').AsString) then
        begin
          qry1.Next;
          Continue;
        end;
        if ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).IsNull then
        begin
          qry1.Next;
          Continue;
        end;
        with qry2 do
        begin
          close;
          SQL.Clear;
          SQL.Add('select count(' + qry1.FieldByName('REFERENCED_COLUMN_NAME').AsString + ') as c from ' + qry1.FieldByName('REFERENCED_TABLE_NAME').AsString);
          if (ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).DataType = ftString) or
             (ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).DataType = ftMemo) or
             (ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).DataType = ftWideString) then
            SQL.Add('where ' + qry1.FieldByName('REFERENCED_COLUMN_NAME').AsString + '=''' + ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).AsString + '''')
          else
            SQL.Add('where ' + qry1.FieldByName('REFERENCED_COLUMN_NAME').AsString + '=' + ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).AsString);
          Open;
          if FieldByName('c').AsInteger = 0 then
          begin
              ref_table := qry1.FieldByName('REFERENCED_TABLE_NAME').AsString; 
              Result := 'code ' + ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).AsString + ' not exist in table ' + ref_table;
              ret_field_name := qry1.FieldByName('COLUMN_NAME').AsString;
              Break;
          end
          else
          begin
            close;
            SQL.Clear;
            SQL.Add('show fields from ' + qry1.FieldByName('REFERENCED_TABLE_NAME').AsString + ' like ''%active''');
            Open;
            ref_table := FieldByName('field').AsString;
            if recordcount &gt; 0 then
            begin
              close;
              SQL.Clear;
              SQL.Add('select ' + ref_table + ' from ' + qry1.FieldByName('REFERENCED_TABLE_NAME').AsString);
              if (ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).DataType = ftString) or
                 (ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).DataType = ftMemo) or
                 (ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).DataType = ftWideString) then
                SQL.Add('where ' + qry1.FieldByName('REFERENCED_COLUMN_NAME').AsString + '=''' + ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).AsString + '''')
              else
                SQL.Add('where ' + qry1.FieldByName('REFERENCED_COLUMN_NAME').AsString + '=' + ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).AsString);
              Open;
              if FieldByName(ref_table).AsString = 'n' then
              begin
                if ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).AsString &lt;&gt; VarToStr(ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).OldValue) then
                begin
                  Result := 'code ' + ds.FieldByName(qry1.FieldByName('COLUMN_NAME').AsString).AsString + ' not active. ' + #13#10 +
                  'Field :' + qry1.FieldByName('COLUMN_NAME').AsString;
                  ret_field_name := qry1.FieldByName('COLUMN_NAME').AsString;
                end;
              end;
            end;
          end;
        end;
        qry1.Next;
      end;
    finally
      FreeAndNil(qry1);
      FreeAndNil(qry2);
    end;
  end;
  </code></pre>