Is there any way to get the primary and foreign key fields from a
table without going into the system tables and extracting them
manually? I can get the primary key using
IndexFields[index].FieldNames but this also returns all other indexed
fields.

Thanks for any help,

Joel

See below file.  Any bugs introduced due due to errors
in transmission.  Unfortunately this routine will only be
able to tell you if a field is part of the primary index (primary
key).  Unfortunately I have not found any database independent
method for determining foreign keys.

{
Unit Description:
This is a utility class to make it easier to determine key
information about a particular field.

Given a particular TField and a TTable, this class will determine
what type of key (if any) the field represents.   This is painful
to write yourself as an TIndexDef.Fields property must be parsed due
to a strange 'feature' of the VCL library which returns keys as
either numbers or strings.

Programmer/s:
        Colin Adams

Modification History:

unit KeyType;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms,
Dialogs,
  DB, DBTables;

{ Type Definitions and variables }
type

  TSFieldIndexType = set of (indNone, indPrimary, indSecondary);

  TKeyType = class
    private

    protected

    public
      constructor Create;
      destructor Destroy; override;
      function CheckWord(field_contains_letters : boolean;
        field_name : string; field : TField) : boolean;
      function FieldInIndex(field : TField; index : TIndexDef;
        var key_type : TSFieldIndexType) : boolean;
      function DetermineIndexType(field : TField; table : TTable) :
        TSFieldIndexType;
  end;

implementation

{--------------------------------------------------------------------------}
constructor TKeyType.Create;
begin
  inherited Create;
end;
{--------------------------------------------------------------------------}
destructor TKeyType.Destroy;
begin
  inherited Destroy;
end;
{--------------------------------------------------------------------------}
function TKeyType.CheckWord(field_contains_letters : boolean;
  field_name : string; field : TField) : boolean;
var
  field_as_number : integer;
begin
  Result := false;

  if (field_contains_letters) then
  begin
    if (field_name = field.FieldName) then
      Result := true;
  end
  else
  begin
    field_as_number := StrToInt(field_name);

    // Compare number to the field number
    if (field_as_number = field.FieldNo) then
      Result := true;  // found it!
  end;

  CheckWord := Result;
end;
{--------------------------------------------------------------------------}
// Determines if the passed in field is contained in the passed in
// index.  If it is, the key type is added to the result set
function TKeyType.FieldInIndex(field : TField; index : TIndexDef;
  var key_type : TSFieldIndexType) : boolean;
var
  i : integer; // loop variable
  fields : string; // the list of fields in the index, this is either
    // field;...;field; or 1;...;N  (where the number is the absolute
    // field number in the table).  This field must be parsed to
    // determine which type it is
  field_contains_letters : boolean; // does the field contain
    // letters (true) or field names as numbers (false)
  field_name : string; // one of the numbers/words in the index
  length_fields : integer; // length of fields string
begin
  fields := index.Fields;
  field_contains_letters := false;

  length_fields := Length(fields);

  // See if the string contains any letters
  for i := 1 to length_fields do
  begin
    if (fields[i] in ['a'..'z', 'A'..'Z']) then
    begin
      field_contains_letters := true;
      break;
    end; // if
  end; // for i

  // Now look at each of the words/numbers in the index in turn
  field_name := '';
  Result := false; // haven't found the field in the index yet!

  for i := 1 to length_fields do
  begin
    // Look for end of word marker
    if (fields[i] = ';') then
    begin
      // Check if the field extracted from the index is this field
      if (CheckWord(field_contains_letters, field_name, field)) then
      begin
        Result := true;
        break;
      end
      else  // Reset field name to nothing
        field_name := '';
    end
    else // Append letter/number
      field_name := field_name + fields[i];
  end; // for i

  if (Result = false) then
  begin
    if (field_name <> '') then
    begin
      // Check the field
      if(CheckWord(field_contains_letters, field_name, field)) then
        Result := true;
    end; // if
  end; // if

  if (Result = true) then
  begin
    if (ixPrimary in index.Options) then
      key_type := key_type + [indPrimary]
    else
      key_type := key_type + [indSecondary];
  end;

  FieldInIndex := Result;
end;
{--------------------------------------------------------------------------}
// Determine what type of key the given field is, in the passed in
// table
function TKeyType.DetermineIndexType(field : TField; table : TTable) :
  TSFieldIndexType;
var
  i : integer;
  found : boolean;
begin
  found := false;

  // Make sure the table indexes are up-to-date
  // (This may slow down the code a bit if the routine is being run for
  // each field in a table as it normally would be.  Could be changed
  // to impose this on the caller function)
  table.IndexDefs.Update;
  DetermineIndexType := [];

  // Look at each index in turn for the passed in field name
  for i := 0 to table.IndexDefs.Count - 1 do
  begin
    // Look for the field in the current index
    if(FieldInIndex(field, table.IndexDefs.Items[i], Result)) then
      found := true;
  end; // for i

  if (not found) then
    DetermineIndexType := [indNone]
  else
    DetermineIndexType := Result;
end;
{--------------------------------------------------------------------------}
end.
{EOF}