Page 1 of 1
How to get primary key fields
Posted: Tue 21 Apr 2009 08:36
by 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
Posted: Tue 21 Apr 2009 08:58
by 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.
Posted: Tue 21 Apr 2009 09:16
by jaska45
It worked! Thank you for fast and good support.
Best regards,
Jaakko
Posted: Tue 21 Apr 2009 09:39
by jaska45
What about foreign keys. What should MetaDataKind be in the case if you want to read foreign keys.
Jaakko
Posted: Tue 21 Apr 2009 10:33
by Plash
UniDAC does not support foreign key columns metadata.
How to choose what is the primary key
Posted: Wed 04 Aug 2010 06:06
by jaska45
Plash wrote: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
Re: How to choose what is the primary key
Posted: Wed 04 Aug 2010 08:32
by 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
Re: How to get primary key fields for Firebird database
Posted: Thu 01 Aug 2013 11:50
by 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;