How to get primary key fields

[jaska45]

Him

I am using Delphi 2009, UniDac and all databases (MySQL, SQL Server, Oracle, Interbase, Firebird, PotsgreSQL). My problem is to get the primary and foreight key field(s). I tried TUniTable.KeyFields but it returns empty string.

How can I get primary key fields? How about foreign key fields?

Best regards,
Jaakko

[Plash]

You can get some information using TUniMetaData component.

If you set MetaDataKind to 'Constraints', you can get a list of primary and foreign keys for a table. But returned information does not include a list of fieds for a key.
UniDAC does not support getting a field list for a constraint.

You can get a field list for a primary key using the following workaround:

Set MetaDataKind to 'IndexColumns', set TABLE_NAME (TABLE_CATALOG, TABLE_SCHEMA if used by the server) in the Restrictions property. Also add the following restriction:
UNIQUE=1

MetaData will return fields for all unique indexes in the table including the primary key.

[jaska45]

It worked! Thank you for fast and good support.

Best regards,
Jaakko

[jaska45]

What about foreign keys. What should MetaDataKind be in the case if you want to read foreign keys.

Jaakko

[Plash]

UniDAC does not support foreign key columns metadata.

[jaska45]

MetaData will return fields for all unique indexes in the table including the primary key.

This works very well but it returns all indexes both primary and foreign keys. I have not manged to figure out which one is the primary key. is there any way to check if the index returned by MetaData is the primary key or not.

Best regards,
Jaakko

[jaska45]

I got this working. When you get constraints information from metadata it gives you a field that tells if the index is primary key.

Jaakko

[stevel]

To get the name of the Primary Key fields, given the table name:

Note that this code works for Firebird database. It requires small changes for other databases:

Code: Select all

// Given an Index-name return the columns that make up that index.
// Note: This code is specific to Firebird database
function GetIndexFieldNamesByIndexName(const AUniConnection: TUniConnection;
  const AIndexName: String): String;
var
  um: TUniMetaData;
  S: String;
begin
  S := EmptyStr;

  um := TUniMetaData.Create(nil);
  try
    um.Connection := AUniConnection;
    um.Restrictions.Values['INDEX_NAME'] := AIndexName;
    um.MetaDataKind := 'IndexColumns';
    um.IndexFieldNames := 'COLUMN_POSITION';
    um.Open;

    while not um.Eof do
    begin
      S := S + um.FieldByName('COLUMN_NAME').AsString;

      if um.RecNo <> um.Recordcount then
        S := S + '; ';

      um.Next;
    end;

  finally
    um.Free;
  end;

  Result := S;
end;

// Given an Table-name return the columns that make up primary key.
// Note: This code is specific to Firebird database
function GetPrimaryKeyByTableName(const AUniConnection: TUniConnection;
  const ATableName: String): String;
var
  um: TUniMetaData;
  S: String;
begin
  S := EmptyStr;

  um := TUniMetaData.Create(nil);
  try
    um.Connection := AUniConnection;
    um.Restrictions.Values['TABLE_NAME'] := ATableName;
    um.MetaDataKind := 'Constraints';
    um.Restrictions.Values['CONSTRAINT_TYPE'] := 'PRIMARY KEY';
    um.Open;

    S := um.FieldByName('INDEX_NAME').AsString;

  finally
    um.Free;
  end;

  S := GetIndexFieldNamesByIndexName(AUniConnection, S);

  Result := S;
end;