How to find database keys that uniquely identify records in TFDQuery?

Question

In short, how do you know which (primary and unique) keys uniquely identify a FireDAC request record?

I am using Delphi 10.1 and FireDAC and Firebird.

I use code generation to create a file containing all the requests of my application. This is an outdated application, so the current database structure is everywhere. The person who designed it did not know (was still studying) when he designed it. Thus, we do not have a coherent system for the unique identification of records. Some of them are in the autoinc int generator. Some have another field for unique identification. Some have combinations. And foreign keys that refer to these fields are also not consistent.

So. Since general database refactoring is not an immediate option, we must deal with it. The best way I could understand is to get all possible keys for a given request. This will allow us to systematically systematically identify all possible ways to uniquely identify the record in the query and table. This will give us an abstraction of the record identifier (mainly a set of keys), which we could pass in our application, and we refactoring.

The first step was to create a virtual database view (table, fields, field types, lengths, keys, foreign keys) with this structure (simplified for readability):

TTable = class(TCollectionItem)
property Name : string;
property Fields : TFieldCollection;
property Keys : TKeyCollection;
end;

TField = class(TCollectionItem)
property Name : string;
property Table : TTable;
property Type : TFieldType;
property Size : integer;
property Required : Boolean;
end;

TKey = class(TCollectionItem)
property KeyType : TKeyType;
property Name : string;
property Table : TTable;
end;

TKeyType = (ktPrimary, ktUnique, ktForeign);

TKeyCollection = class(TCollection)
end;

TTableCollection = class(TCollection)
end;

TSchema = class(TObject)
property Tables : TTableCollection;
end;

Secondly, I was able to create code from this schema for each application table along with all possible keys. Looks like that:

CUSTOMERS table
---------------
ID int (autoinc, pk PK_CUSTOMERS)
CODE int (uk UK_CODE)
NAME string
GROUP_ID int (fk on GROUPS.ID)


GROUPS table
------------
ID int (autoinc, pk PK_GROUPS)
NAME string

// Generated code (simplified for readability)

TCustomerPk = class(TKey)
property ID : Integer;
end;

TCustomerCode = class(TKey)
property Code : Integer;
end;

TCustomerKeys = class(TKeys)
property CustomerPk : TCustomerPk ...
property CustomerCode : TCustomerCode ...
end;

TCustomers = class(TTableBase)
property ID : TIntegerField;
property Code : TIntegerField;
property Name : TWideStringField;
property Keys : TCustomerKeys; 
end;

TGroupsPK = class(TKey)
property ID : Integer;
end;

TGroupsKeys = class(TKeys)
property GroupsPk : TGroupsPk;
end;

TGroups = class(TTableBase)
property ID : TIntegerField;
property NAME : TStringField;
property Keys : TGroupKeys;
end;

, , ​​. - , .

, :

• ,
• ,

( ):

for lQueryField in lQuery.Fields do
begin
  if not lTableList.Contains(lQueryField.Table)
    lTableList.Add(lQueryField.Table)
end;

for lQueryTable in lTableList do
begin
  for lKey in lQueryTable.Keys do
    begin
      lFound := true;
      for lKeyField in lKey.Fields do
      begin
        if not lQuery.Fields.Contains(lKeyField) then lFound := false;
      end;

      if lFound then
        // Query has key lKey

    end;
end;

TFDQuery

SELECT CUSTOMERS.ID CUSTOMERID, CUSTOMERS.CODE CUSTOMERCODE, GROUPS.ID GROUPID FROM CUSTOMERS INNER JOIN GROUPS ON GROUPS.ID = CUSTOMERS.GROUP_ID

, , PK_CUSTOMERS PK_GROUPS, :

TCustomersQuery = class(TQueryBase)
property CUSTOMERID : TIntegerField;
property GROUPID : TIntegerField; 
property CUSTOMERCODE : TIntegerField;
property CustomerPk : TCustomerPk;
property CustomerCodeUk : TCustomerCode;
property GroupPk : TGroupPk;
end;

, , . , TCustomersQuery CUSTOMERS. , TCustomersQuery , CUSTOMERS.

TCustomersQuery GROUPS.

, PK_CUSTOMERS UK_CODE , PK_GROUPS - .

, PK_CUSTOMERS UK_CODE , PK_GROUPS.

, .

FireDAC ProviderFlags IDE. - , . , , . , ProviderFlags = pfInKey?

, , ( ) FireDAC?

Answer 1

, FireDAC, .

( ) , . ( )

, " ", , ( PK/AK). .

, ( ).
, , ( ).

Becareful!!
.

select t1.PKColumn, t2.PKColumn
from Table1 t1
cross join (
    select top @ExternalParameter * 
    from Table2
) t2

ExternalParameter = 1 t1.PKColumn

, sql.

Firebird, SQLServer, , ​​ , Firebird ( )

SQLServer sys.dm_exec_describe_first_result_set :

select column_ordinal, d.name, source_table, source_column, is_part_of_unique_key, is_identity_column, c.object_id, c.column_id
from sys.dm_exec_describe_first_result_set(N'SELECT CUSTOMERS.ID CUSTOMERID, CUSTOMERS.CODE CUSTOMERCODE, GROUPS.ID GROUPID FROM CUSTOMERS INNER JOIN GROUPS ON GROUPS.ID = CUSTOMERS.GROUP_ID',null,1) d
join sys.columns c on c.object_id = OBJECT_ID(d.source_table) and c.name=d.source_column
where (is_hidden=0 and is_part_of_unique_key=1 or is_identity_column=1) 

:

select *
from sys.indexes i 
join sys.index_columns c on i.object_id = c.object_id and i.index_id = c.index_id
where c.object_id = @TestObjectID 
and exists (
    select 1 
    from sys.index_columns cc 
    where cc.object_id = c.object_id and cc.index_id = c.index_id and cc.column_id = @TestColumnID
)

delphi, - , :

test_uniqe := 'if exists(select ' + SourceColumnList + ', count(*) from ' +  SourceTable + ' group by ' + SourceColumnList + ' having count(*)>1) select 0 uk else select 1 uk

! , PK/AK, , , .

.

, - .